Need Help with mysql table structure !

[Anonymous]

suppose that I have two table created :

parts(part_id,part_cat,part_name,part_descript,part_pic,part_price)
1,'wheel','storm wheel','No description','st_wheel.gif',250
2,'wheel','normal wheel','No description','nor_wheel.jpg',150
3,'gearbox','turbo gear','No description','tur_gear.png',4500
4,'gearbox','normal gear','No description','nor_gear.jpg',3000
5,'door','10mm door','No description','ten_door.jpg',600
6,'tyre','good year','No description','gy_tyre.jpg,120
7,'tyre','continental','No description','c_tyre.jpg',120


list(lisst_id,wheel,gearbox,door,tyre)
1,1,3,5,6
2,1,4,5,6
3,2,4,5,7
// wheel,gearbox,door,tyre keep only the part_id of parts table

and let say if I want to get all the part_pic for list_id number 2
how will the SQL look like ?


I know that we can join the parts table 4 times (in this example) and give it a name each time we join the table, then get the data that I needed, but this example is just a small example, with the actual database, parts(table) may contain more fields and thousands of records, and list table contains not only these 4 parts, it actually contains 18 parts and could be more, which means if I join parts table 18 times, it will be a big disaster... so ... is there any other way to do this ?

actually I post this question on forum of mysql.com and I got the response from Anthony Willard :

You've violated a normalization rule in how you've setup your "list" table.

You need a many-to-many relationship; you need a parts table, a list table, and a parts_list table. Then you can join the list and parts table through the parts_list table to see any/all parts.

You've already given the parts a basic type identification (ie wheel, gearbox, etc.), so you can limit your queries to just parts of interest instead of showing all parts when you only want to know about a few.

anyone ~ please show me the structure of those three tables that Anthony is talking about ~ or is there any other way to solve the problem ~ :help:

Billy ~