I been doing some research on normalization for mysql db. I realized that it's much more easier to have multiples tables rather than one table. I'm guessing it's much more easier to have a column for different values than combining it in one column. My question is what if I am designing a page that has a fixed info and I will always need to pull those info together. For example I have a column called T-Shirt Colors under the table CLOTHES. I would then store the value "Red - Blue - Green - Yellow - Orange - Pink - Dark Red " etc. and let's say that column value will have a very long string since it will contain over 1,000 colors. I know for SURE that these are the only colors my store will sell so I don't have to worry about adding or deleting the colors... Would it be more efficient on the overhead and server speed to do it this way and then explode the colors into a list...
I've been recommended to create a linked table for the colors... However:
Database Design A: (linked table design)
Table products - ProductID : - Name: - Shirt:
Table colors - ColorID: - Name:
Table product_colors - ProductID: - ColorID:
the numbers of tables and rows I would get based on 1,000 colors for a t-shirt would be:
Number of Tables : 3
Number of Columns : 6
Number of Rows : 2,002 -----> 2 ( from table products), 1,000 (from table colors), and 1,000 (from table product colors)
if I was to setup a table with three columns and just store all the colors together in a long string for the column colors (and explode it into a list when needed).... the amount of rows and columns I would get is:
Database Design B: (storing 1,000 colors in one long string and then exploding it into a list )
Table shirt_colors
Product ID:
Name:
Colors:
Number Of Tables : 1
Number Of Columns : 3
Number Of Rows : 3
Can anyone explain to me how Database Design Option A is more optimal (overhead and performance wise) than setting the colors into one long string value and then exploding it (besides the fact that it's easier to read and maintain)?
Database Design Option A : would result in 3 tables, 6 columns, and 2020 rows being created.
Database Design Option B : would result in 1 tables, 3 columns, and 3 rows being created.
I'm having a hard time understanding how Database Design Option A would be the more optimal choice from an overhead and performance perspective. I do realize that a column with a value of over 1,000 words can be very long, but I'm thinking it has no problem pulling up long strings since mysql is a very popular among article and forum sites.
Also if it is more efficient to explode the values (even though it's harder to maintain) on the overhead and server speed, approximately how many more times is it it more efficient.. 1X faster, 2X faster, 3X faster, etc. or maybe too little to be even notice? Thanks in advance
(keep in mind that these 1,000 colors are always going to be used together... Since they will always be pulled and used together to give the customer the color options to select from...)
