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 开发者_StackOverflowincrease 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

---

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.

---

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.

---

You may only need two tables:

MainPartTable
   *ID
   Name 

SubPartTable
   *MainPartTable_ID
   *SubPartTable_ID
   Name

---

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