0

I am struggling with how to normalize a database where there is multiple addresses pr. user.

The situation is that a user can have both a regular address and a delivery address. When a user make an order, one of the addresses should be connected to the order.

I am trying to achive a 3NF normalization. So i will store the addresses in a junction table, should i add an primary key to user_addresses table and use that in fk_address_id column in orders table? or would that be breaking the 3NF?

What i am basically trying to do is to make it possible to make an order with a diffrent address. enter image description here

Improve this question
1
  • 1
    Please clarify via edits, not comments. Please delete & flag obsolete comments. Please decide what your 1 question is & give just the DDL: for the relevant tables involved of the relevant design(s) involved. Say what you can about why you are stuck but you should be following a published DB design method so which is it & what step are you stuck on & (as clearly as you can) why can't you do that step? Your edit just made things worse, adding to something unclear doesn't make it clear & what you added isn't clear. Cut your post down to the very 1st place you are stuck.
    – philipxy
    Commented Feb 1 at 0:02

2 Answers 2

Reset to default
0

Consider a couple of things:

  • Can an order be placed with a custom address, which is not one of the addresses associated with the user? For example, when a user orders a holiday gift and asks it to be shipped directly to the gift recipient. If so, then don't reference the user_addresses table. This would limit shipping destinations to those associated with the user.

  • Can address records be changed after the order is placed? Do you need a record of where an order was shipped at the time? If so, then you need to store a copy of the address details, to preserve what the address was on that date. This might seem like it conflicts with 3NF, but it's storing a different fact — that of the address value at a certain time, even if it is subsequently updated.

Improve this answer
2
  • 1
    Exactly i need to be able to assign a diffrent address to the order other than the users address, and i would need to be able to change the address of the user after the order has been made, so i guess the correct appoach would just be to store the address In the order it self
    – Soma Juice
    Commented Jan 31 at 23:33
  • Yes, that's what I would recommend.
    – Bill Karwin
    Commented Feb 1 at 13:48
0

Get rid of user_addresses; instead, have an address_type (varchar) in the addresses table. That also gets rid of the over-normalized "address_types" table.

Address_types might be "billing", "mail", "gift" (many). Have the UI ask if the billing address is the same as the mailing address, thereby saving the user some irritation.

As Bill points out, a "ship to" belongs with the order, not the user. (Meanwhile, it is possible to follow the links user <-> order <-> ship_to

I would not bother de-duping your address and your partner's.

Is a Professor demanding that you use 3NF? If so, I can't help you.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.