Skip to main content
2 of 3
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

Storing Address Data Model

I'm revising our customer database from a one-to-one relation to a one-to-many. Specificaly we are introducing a new billing address and a shipping address. When researching I came across this post Storing a Billing Address Best Practice in Orders Table and this post Is there common street addresses database design for all addresses of the world?

Thinking about this I have some questions. I'm seeking answers so to implement the data model as provided by both answers, but currently I have doubts about implementing it this way, even though I understand from a technical point of view this is the way to do it. At the moment my main question is about the relation between the Address and CustomerAddress table.

We will have customers enter there own address. If they make a mistake and not receive anything it's their bad. This means two people can live at the same physical address, but have a slightly different printed address. One customer could for example abbeviate 'boulevard', but another doesn't have to do that. We want to implement it this way so when someone changes his address the address for different people on the same physical address doesn't change as well. So there will be no standardized address table. (If I would want that and still not want the aforementioned scenario I would my self have to organize an administrative process to create standardized addresses. This is not going to happen.) Every address record will be unique to a customer. So I think there is a one-to-one relation between Address and CustomerAddress.

The one-to-many relationship for addresses is provided, as in both data models in the provided links, through multiple records in the CustomerAddress table. The CustomerAddress table provides different purposes for each address through either a bit (data model 1 ) or a type (data model 2). Changes to these can be recorded in the CustomerAddressHistory table.

Due to the one-to-one relation between Address and CustomerAddress I thought about merging Address and CustomerAddress, but that would mean more storage when changing only a 'purpose' bit or type in the CustomerAddressHistory table.

So the main question is what would be the scenario that is the main objection to not implement a one-to-one relation between Address and CustomerAddress?

Sigur
  • 123
  • 1
  • 4