Hello to All!

I want some help in my DB design. I have a Inventory DB, in which there is a Products table. Now each product can be available in multiple Types, Materials, Colors, and Themes etc. But I have to have only one record per product in the Products table no matter in how many colors or materials it is available in. Then I have multiple tables each for Materials, Colors etc.

One way of connecting a product with different colors, Materials is having a table in b/w them that'd contain ID's from both Product and Colors, Materials Table like

IDProduct IDColor and one table like

IDProduct IDMaterial

But this would end up in having alot of those in b/w tables, One each for Materials, Colors, Types, Themes and so on.

One other way might be to save the info in the Products table it self in a comma separated field like:

IDProduct IDType IDColor IDMaterial ...
1 1,2,3 2,3 3,5,6

Tell me which of these or some other is better way to save such information.


I'd be very grateful.



Regards,
Sajid.

This is exactly what you should do. If you want to relate two tables together, you need to create a relational table. At least if you are going to have an m-to-n relationship, which you do.

Are you worried about space? The datatypes of the columns in those tables will not take up much room, and you'll really need to have a LOT of information in there to cause any sort of noticeable speed issue. Even if that does ever happen, if you have proper indexing, everything should be fine.

If you did that, what would you do if you wanted to change some of the relationships after they had already been entered ? There would be some ugly parsing going on.