superjadex12
01-21-2007, 06:28 PM
I am working on mysql database and have bought a few books, and researched extensively online. I was even enlightened by learning about db normalization and that has probably been the most significant breakthrough I've made. Normalization is GREAT, i love the logic behind making effecient tables!
So with that I have one scenario that hopefully someone can help me out with designing it effeciently.
Let's say I sell shoes and have a "product_table" that lists every shoe I am selling. the table fields are product_id, brand, price, size, and color.
Now brand would be a foreign key linking to the brand_tabe, price is static,
but what about size and color?
If i use a separate table to record size info, and I had 10 available sizes
then that would mean that every product could potentially have 10 records each, in the size table :
ex: product_table.product_id = size_table.fk_product_id
(lists matches)...
1-size 1
1-size 2
1-size 3
2-size 1 ...
As I understand it, this would be the BEST way to record information since it lets you search and sort by size. i.e a customer wishes to see all shoes available in size 4. But what if i had 1000 products and 50 sizes. I just feel that the size_table may get uneccessarily bloated (or is that very necessary ?) ?!
Another Idea I had was to record the sizes in the the product_table, directly in the field "sizes". I could format my size field like this : 1,2,3,4,5 and list all available sizes in that manner.
I can still gain access to each size by use of php's explode/implode.
Is there a way that I can search acurately by size by searching the size field ?
Also, suppose i had 100 sizes (extreme example) would listing them all in the latter manner still be uneccessarily bloating and bad table structure?
OR is there some miracle way that I am not seeing, or haven't gotten a chance to realize/learn?
I appreciate anyone's thoughts! Thanks again to all the forum members and web authors, past and present who have helped me learn so much.
So with that I have one scenario that hopefully someone can help me out with designing it effeciently.
Let's say I sell shoes and have a "product_table" that lists every shoe I am selling. the table fields are product_id, brand, price, size, and color.
Now brand would be a foreign key linking to the brand_tabe, price is static,
but what about size and color?
If i use a separate table to record size info, and I had 10 available sizes
then that would mean that every product could potentially have 10 records each, in the size table :
ex: product_table.product_id = size_table.fk_product_id
(lists matches)...
1-size 1
1-size 2
1-size 3
2-size 1 ...
As I understand it, this would be the BEST way to record information since it lets you search and sort by size. i.e a customer wishes to see all shoes available in size 4. But what if i had 1000 products and 50 sizes. I just feel that the size_table may get uneccessarily bloated (or is that very necessary ?) ?!
Another Idea I had was to record the sizes in the the product_table, directly in the field "sizes". I could format my size field like this : 1,2,3,4,5 and list all available sizes in that manner.
I can still gain access to each size by use of php's explode/implode.
Is there a way that I can search acurately by size by searching the size field ?
Also, suppose i had 100 sizes (extreme example) would listing them all in the latter manner still be uneccessarily bloating and bad table structure?
OR is there some miracle way that I am not seeing, or haven't gotten a chance to realize/learn?
I appreciate anyone's thoughts! Thanks again to all the forum members and web authors, past and present who have helped me learn so much.