Multiple Primary Keys as single foreign key

问题:

I have a requirement to design 2 or more tables. 1. SubParts table 2. MainParts table.

A single MainPart can have multiple subparts. I am thinking of doing something like

SubPart table: Id and Name

MainPart table: Id and Name

SubPart_MainPart relationship table: MainPart_ID SubPart_Ids(array or comma seperated)

Is there a way to put multiple subpart_id in single column in the relationship table? or should I use MainPart_D and SUbPart_ID as combined primary key in the relationship id?

the second approach will increase lot of records in the relationship table. where as the first approach will increase looping the code when I try to iterate the comma separated column(SubPart_Ids).

Do you have any other approach for this?

thanks for your help

回答1:

Does the SubPart have one or multiple mainParts?

If only one MainPart, then all you need is the SubPart and MainPart table; which the MainPart ID in the SubPart table.

If multiple MainParts, then you need a SubPart_MainPart table. This should NOT be a comma seperated list. Each row should be one link between a MainPart and SubPart.

回答2:

Fisrt rule of database design, do not ever store anything in a an array or comma delimited list. You want a join table that has the mainpart id and the subpart id and one record for each subpart. It will be easier to query and probably faster if correctly indexed.

回答3:

You may only need two tables:

MainPartTable
   *ID
   Name 

SubPartTable
   *MainPartTable_ID
   *SubPartTable_ID
   Name

回答4:

Having a list of sub part IDs would be violating 1NF and is highly discouraged in almost any circumstance. If you're using any kind of a DBMS then having more rows in your intersection table is not a problem.

Another thing to think about is why you have a separate sub-parts table. The conventional way to do this sort of thing (so-called "bill of materials") is to have one table for parts/assemblies and one table to say "these are the things that make up that thing". In other words:

ASSEMBLY
- Assembly ID (PK)
- Assembly Name 

COMPOSITION
- Contained In Assembly ID  (PK, FK)
- Contains Assembly ID (PK, FK)

Edit: Note too that a real bill of materials would have fields in the COMPOSITION table for quantity and unit of measure (of the quantity).