CODEWITHSUNDEEP
sqldatabase
Nathan Arendt
Nathan Arendt

Reputation: 13

Is it best to store address information with the order table or in it's own table?

I am designing a database and initially I setup addresses separate from orders so the orders table just has a billing id and address id reference and the actual address are stored in the address table.

However the more I think about this i am not sure there is any real advantage to putting address into their own table.

The main reason is that a order is self contained meaning that the address is only valid for that order.

Having address linked instead of on the order record leads to some complications when updating addresses. Say if we have two orders from one customer linked to the same address and he wants the address changed on one order but not the other a new address. The application now needs to create a new address in the address table and change the linked address on the order (you can't just change the address because it would change it for both linked orders).

You also have to have code in your application that links new orders to addresses already in the database (and that only works for orders where the addresses are identical or nearly identical).

The only advantage I see right now to having order addresses in their own table is so the billing address and shipping address are not duplicated for every order.

On the surface it seems like preventing billing and shipping address data duplication isn't worth the extra code needed to make storing addresses in their own table work.

Any recommendations on how to handle this? Is there some big advantage to handling the addresses in separate tables that I am missing?

Upvotes: 1

Views: 1940

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

It sounds like what you want (ideally) is for the order to be linked to the address as it was at the time of the order.

There are two main ways to implement this - either the order includes all of the address information within its own structure, or you implement the address table as a temporal table, and ensure the order has appropriate datetime information to locate the correct address rows when queried.

Which one you implement depends on how you wish to handle updates - both models may have issues, depending on whether in-flight orders should/should not be updated.

Upvotes: 2

Adam Robinson
Adam Robinson

Reputation: 185613

If the address is linked to that order only, then it should be part of the order, not in its own table.

Unless your database has logic relating to an actual address entity, it doesn't need to be in its own table even if you'll want to add the ability to store address information for other sorts of entities.

Upvotes: 1

Related Questions