1

Having a very simple table shema:

order_id (pk), customer_id(fk), order_date, ship_date, ship_address

Is such a table in 2NF? If so is it also in 3NF?

Thx for help:-)

MulliGan
  • 29
  • 6
  • Probably should have ship_address_id, so you can allow customers to have multiple addresses (shipping, billing, contact, etc.) – ps2goat Oct 06 '13 at 06:07
  • You may want to consider separating order information and shipping information. A single order may have multiple shipping events – James A Mohler Oct 06 '13 at 06:13

2 Answers2

0

I think it's the 2NF, because there could be one more useful table.

3NF should look like:

# orders
order_id (pk)
customer_id (fk)
ship_address_id (fk)
order_date
ship_date

# customers
customer_id (PK)
customer_field1
...

# addresses
address_id (PK)
customer_id (FK)
street
zip
...

Amazon allows to add several addresses (home, office, ...). You should do it as well! :-)

Mr. B.
  • 8,041
  • 14
  • 67
  • 117
  • Amazon also allows things to be shipped separately or together – James A Mohler Oct 06 '13 at 06:23
  • What about more than 1 ship_date ? – david strachan Oct 06 '13 at 07:52
  • For allowing that, you'd need to have something like a shipment table, with a one(order) to many(shipment) relationship. The table would have `shipment_id (pk), order_id(fk), ship_date` (this would be removed from orders), and anything else relevant to the specific shipment (maybe packaging type, shipping carrier, etc. since these can also differ between orders) – user2366842 Jul 28 '14 at 18:04
0

your order table should be split into orderDetails with a sibling of a product table and ShipDetails. The order table should only have attributes that pertain to the order like total quanty of products for order or order date. the orderDetail will have productID and quantity of each.

The rule of thumb is "the key, the whole key and nothing but the key"

crhenry79
  • 1