PDA

View Full Version : 2 examples of mysql table_structure, which is better ?



superjadex12
01-21-2007, 05: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.