Normalization Question

[xpartmgr]

First let me thank everyone who has answered questions here. I have used this site and others to gain a good understanding of database design. :)

Here is my question. When normalizing a database can you have multiple IDs in the same "link" table. Here is an example of what I mean.

Parts Table
PartNumberID
DescriptionID
Qty
RemarkID
SpecificationID

Of course each ID is a foreign key that links to a primary key on another table (i.e. PartnumberID links to the PartNumber table).

There have been many people in my department that feel when an ID is duplicated in this table it is no longer normalized. I looked online and generally they use very simple examples that would never answer this question.

Please all you DB experts any information would be helpful.

Thanks

[jbisono]

Well first of all you have a very nice understanding about db, in fact primary key in other words means, that specific piece of data that contain partnumberid cannot be repeated for the all history of that table.

As you said foreign key make reference, to another table in order to make a link between to tables you can have as much foreign key as you need it.

Back to primary key you can have multiple primary key in a single table but what should be the reason to select partnumberid and descriptionid as primary key, if the partnumberid is more than enough. if you set your table up correctly the db management system would not let you save a duplicate id ever.

a simple example when you have more than two primary keys in a simple table is "imagine have a order table, of course an order can have different products so you will have two tables one for the order header in another one to hold the products.

[U]Order[/U]
OrderId pk
Date
Client

[U]OrderLine[/U]
OrderId pk
Line pk
partid fk

Take care.

[xpartmgr]

Thanks for your quick response.

I left out that the parts table would have a PK of it's own. I need this to identify a unique record without having a composite key. I basically need to link a part to the page it was on in a parts manual.

Some people here think that having a table with 5 or 6 fk links would not be normalized. Like this.

Parts Table
PartID pk
PartNumberID fk
DescriptionID fk
Qty
RemarkID fk
SpecificationID fk

I would then link the PartID back to the Page this part came from. Never would there be two records exactly the same in the parts table (I check this during my import and never write if it already exists). I have been fighting this battle with them for some time and their final solution includes dozens of tables. I can't imagine this would perform well once it goes to production.

[Ezzaral]

I fail to see why you would need both a PartID and a PartNumberID. Is it at all feasible that two parts would share the same part number?

I would ask the same of DescriptionID. Why not just a Description field on the Parts table? Unless two completely identical parts could vary by SpecifcationID?

Obviously I don't have the broader view of your table relationships and data domain, but I would think one table for Parts:
Parts
ID pk
Description
SpecificationID fk

and a separate table for quantity information/page info/etc :
Quantities
PartID
Qty
Remark

I would agree with your assessment of the "dozens of tables" ultra-normalized approach leading to a nightmare in production. You don't want a simple part query to involve 12 joins because every single piece of data is it's own table and you have nothing but 12 IDs in "Parts".