Multiple Primary Keys as single foreign key

Question

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

Joel Brown · Answer 1 · 2011-04-20T15:06:11.610

0

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).

edited Apr 20 '11 at 15:06

answered Apr 18 '11 at 19:40

Joel Brown

14,123
4
52
64

So what you are suggesting is ASSEMBLY ASSEMBLY_ID NAME 1 Engine Oil 2 Engine Chain COMPOSITION COMPONENT_ID ASSEMBLY_ID 1 1 2 1 3 1 4 1 2 2 4 2 – narayanan Apr 18 '11 at 19:42
I'm not sure that a chain and oil are a good example since one isn't a part of the other. I would say that part of your database might look like ASSEMBLY: { 1 Engine; 2 Oil; 3 Chain} and COMPOSITION { 1 2; 1 3 } In other words, Engine contains Oil and Engine contains Chain. Note the edit above too regarding quantity and units of measure. – Joel Brown Apr 20 '11 at 15:05

score 0 · Answer 2 · answered Apr 18 '11 at 19:41

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.

jColeson · Answer 3 · 2011-04-18T19:48:26.760

0

You may only need two tables:

MainPartTable
   *ID
   Name 

SubPartTable
   *MainPartTable_ID
   *SubPartTable_ID
   Name

edited Apr 18 '11 at 19:48

answered Apr 18 '11 at 19:43

jColeson

951
1
14
24

score 0 · Accepted Answer · answered Apr 18 '11 at 19:43

0

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.

answered Apr 18 '11 at 19:43

Richard Schneider

34,944
9
57
73

Its the other way round. Multiple Sub-parts can be used in single Main part. 2 main parts can use same sub part too – narayanan Apr 18 '11 at 19:47
OK, so use the second approach. – Richard Schneider Apr 18 '11 at 19:48

Multiple Primary Keys as single foreign key

4 Answers4

Linked