0

I am in the process of designing a MySQL schema for a website which allows the visitors first to view the list of products (browsing or searching) and view each product in details. Only certain fields are displayed in the listing view and a lot more information is to be displayed when viewing the details, with some fairly big column data (e.g, the product description). The design is quite common. I am wondering what is a good way to store the product information.

One choice is just to have one PRODUCT table. Viewing the list will have a select a subset of the fields, say, name, price and the main product picture, but not including other fields like description which could be fairly big, say, a VARCHAR(2000). My question is that I do a select name, price, main_pic from product where ..., will the description fields also get loaded into the memory by MySQL engine, thus consume more spaces?

If MySQL does load other unselected fields into memory or simply just want to keep details in another table, there could be a PRODUCT_DETAIL table. Would this be a good design? I feel a little weird since both PRODUCT and PRODUCT_DETAIL would then have the same primary key, and because of the master-detail relationship between the two, the primary key of the PRODUCT_DETAIL would also be a foreign key referencing the primary key of PRODUCT! Anyone actually use this kind of design?

hanaZ
  • 261
  • 2
  • 13

1 Answers1

0

This question is similar and can help you.

Question : select * vs select column

And yes Master-detail type relationships are also used and is a good way to design.

Community
  • 1
  • 1
P. Jairaj
  • 1,033
  • 1
  • 6
  • 8
  • Thanks much for the pointer. I've read through the discussion. However I am not entirely clear on what are the conclusions. As far as the overheads of `select *` vs `select column` are concerned, what is what I see: 1) disk I/O overheads are the same, as DB engines always pull in the entire atomic tuples for each row 2) there would be less network I/O overhead for `select column` 3) there is no clear conclusion on memory I/O overhead but I reason should be about the same on the server side as the same tuples are read in but less for `select column` on the client side – hanaZ Jan 24 '16 at 07:37
  • A related discussion in [link](http://stackoverflow.com/questions/1125004/which-is-more-efficient-multiple-mysql-tables-or-one-large-table). It seems make sense to separate tables even though there is a one-to-one relationship, In the case of two table design, should both tables use `product_id` as the primary keys, or the `PRODUCT_DETAIL` uses `product_detail_id` as primary key and have `product_id` as a foreign key? If both uses `product_id` as primary keys, how do I ensure they are identical for the same product with auto generated `product_id` when records are saved by OR mapping tools? – hanaZ Jan 24 '16 at 08:05
  • You can have a column as primary key as well as foreign key. So no need to have autoincrement in *product_detail* table. Just insert its value as you would have for separate foreign key – P. Jairaj Jan 24 '16 at 08:10