1,105,177 Community Members

db modeling

Member Avatar
sasha_3
Newbie Poster
1 post since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Attachments date.JPG 29.02KB table.JPG 26.79KB
Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 

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.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: