0

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.

Edited by Triztian: Clarifiying

2
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by Triztian
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.