db modeling

[sasha_3]

Hi everybody,

I have made my ER diagram for my DB, but i want it optimized.
The db should store date for something like in attached images.

I thought about a Products table with attributes: some_primary_Key, product_id, component_id,...
The number of component is stored in another table; the question is how do i structure this dates so that i don't have to many lines in tables and redundancy.
As i made it for now i would have to many lines in Products table, a row for each component_id of a product_id; maybe a can reduce this with some db structure concept, 'cause i could have same component in more products (and reverse of course).
Should i make 2 tables? Products and Components?... please advise me. What should i google about?

[drjohn]

The second way is normalised, and great simplifies querying - you don't have to search multiple columns.

Apart, that is, from your failure to recognise that product and component are a natural joint primary key. There is no need to add another unique identifier, as it will cause you problems. For example you will have to declare the combination product with component unique and force the database to check this separately from checking the unnecessary id. Declaring them as a joint primary key makes this unique check automatic, and saves the extra check on your extra id.

So your table is just the two columns product_id and component_id.

PS you should be googling on database normalisation.