How to 1NF'ize this tables.

[Triztian]

Hello all hope this section isn't abandoned, anyway any help is really appreciated.

Heres my problem:

So I'm designing this database to hold orders, a customer places an order, which can have up to four accessories, at first I designed this table:

• idOrder
• Acc1
• acc2
• acc3
• acc4

but then I realized that it wasn't 1NF compliant, so I redesigned it to the following:

• idOrder
• accessory

The problem is that this table also does not comply with 1NF since it cannot have a Primary Key because both field values may repeat, here's why:

IDORDER  | ACCESSORY 
---------+----------  >> Repeated OrderID
  520    |   A16 <<   << Repeated Accessory ID
---------+----------
>> 140   |   A34
---------+----------
  320    |   A16 <<
---------+----------
>> 140   |   A25 
-------------------

See one order may have up to four accessories(meaning that it's id appears up to four times) which don't repeat for that particular Order, but a different order may have the same accessories so the value of both fields may repeat, a more enlightening example:

IDORDER | ACCESSORY 
--------+---------- << Repeated Accessory.
   52   |   A16 <<  ** And each order (in this case) has four accessories.
--------+----------
   52   |   A34 <<
--------+----------
   52   |   A10
--------+----------
   52   |   A25 
--------+----------
  255   |   A16 <<
--------+----------
  255   |   A34
--------+----------
  255   |   A15
--------+----------
  255   |   A45 
-------------------

Any solution to this? any help is really, really appreciated.

[vijaykrishnabor]

i think you can make composite primary key =(IDORDER+ACCESSORY ) it will work for your problem

[Triztian]

I think I've found the solution, I have added a third field called idAccBundle, which identifies each bundle of accessories that is sent per order, also as a note an order is able to generate up to four idAccbundle records.

Thanks for the reply by the way.
Regards Tristian.