mysql products db design

Question

I am building a products site with php and mysql(no shopping cart).

I have 400k products, the main site with 2 methods for query.

1. query for products types, all the query by menu, cloud tags (no custom form search)

2. query for products details, custom form search, search with products name, details.

Each products have these fields: name, details, add_date, catalog, brand. The main problem is each products would have multi catalogs. The total catalogs have more than 600. And each product would have 3-10. For example:

(name) SAUCONY LADY PROGRID RIDE 3 RUNNING SHOES 
(details) This ProGrid Ride 3 is a neutral running shoe designed for the keen female runner who is competing at a decent level.
(add_date) 1333128354
(catalog) sport women USA pink leather
(brand) nike

So in my require, how to design this db?

---

I am not good at mysql, I have 2 ideas.

• Use Innodb, make 2 dbs

db1

id | name | details | add_date | catalog | brand
1 | some name | some details | 1333128354 | 101 201 301 401 501 | nike
2 | some name | some details | 1333128355 | 101 202 | nike
3 | some name | some details | 1333128357 | 102 202 334 401 | puma

db2

cid | catalog
101 | sport
201 | man
202 | woman
301 | USA
302 | Franch

Use select * from db1 where catalog like '%101%' to get all the sport shoes, use Rlike to do the custom form search.

• Use MyISAM, make 3 dbs

db1

id | name | details | add_date | brand
1 | some name | some details | 1333128354 | nike
2 | some name | some details | 1333128355 | nike
3 | some name | some details | 1333128357 | puma

db2

pid | catalog
1 | 101
1 | 201
1 | 301
1 | 401
1 | 501
2 | 101
2 | 202
3 | 101
3 | 202
3 | 334
3 | 401

db3

cid | catalog
101 | sport
201 | man
202 | woman
301 | USA
302 | Franch

Use SELECT * FROM db1 INNER JOIN db2 ON db1.id=db2.pid WHERE db2.pid='101' to get all the sport shoes, Add fulltext index to db1.name and db1.details, use fulltext search to do the custom form search.

---

Or other better ideas? I need a fast way and occupy lower CPU and RAM. many thanks.

• ypercubeᵀᴹ: That 1333128354, is it an integer, a timestamp or a date? Do you know the various datetime datatypes in MySQL? There is DATE, TIME, DATETIME and TIMESTAMP. If you are storing only dates, then you probably should use the DATE type.

Answer 1

I am not a database designer.

You don't make dbs but tables.

Both of your table structures can be implemented as innodb tables and as MyIsam tables.

Your first idea is not a good design. It violates first normal form, so i would propose the second idea with innodb. the table db1 can be decomposed further in two tables

id | name | details | add_date | brand
1 | some name | some details | 1333128354 | 1
2 | some name | some details | 1333128355 | 1
3 | some name | some details | 1333128357 | 2
id | brand
1 | nike
2 | puma

Answer 2

1. First of all since you are using MySQL, go for InnoDB which allows you to create relationships between the different tables (not dbs)

2. You need three tables:

a) categories (db3) - I think you will use this for the tag cloud

b) products (db1) - name and details of each product

c) product_categories (db2) - a single row for each product and each category

1. Through the tag cloud you show a category name but pass an id - show sport, but pass 101

2. TO get all the products in category sport

   SELECT * FROM db1 INNER JOIN db2 ON db2.pid = db1.id WHERE db2.catalog = 101