Database Design – Storing Address Data Model

best practicesdatabase-design

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?

Best Answer

When working with addresses, one thing you may consider -- at least give it a serious look -- is using the USPS to convert the user-supplied address into the USPS standard address. So the same address, just with different abbreviations or punctuations, will converge to the same address. There are several remote apis the Post Office makes available to your app.

As for the database, yes, the literature shows that the place to put a 1-to-many FK is in the "many" tuple. You have also stated you want to maintain a history of address changes so this format makes even more sense.

However, there are many advantages to having a 1-to-many intersection table. This is similar to a many-to-many intersection table except that one side is made part of the PK to enforce the "1" side of the 1-to-many cardinality.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  constraint PK_OneToMany primary key( OneID, TypeID )
);

Each user has only one entry for each kind of address and even if multiple users share the same address, if one user moves to a different address, the other users are not affected.

There is a small alteration needed to maintain the history of changes. All that is needed is the addition of a datetime field.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  Effective datetime not null default NOW,
  constraint PK_OneToMany primary key( OneID, TypeID, Effective )
);

Now all changes are maintained and it is very easy to look back at any time in the past to see what the address was at that time. Here is an answer I posted that will refer you to more information. Mine is not the accepted answer, and I would highly recommend you take the time to become familiar with both mine and the accepted answer. Choose the one (or design your own with what you learn) that is best for your situation.

Related Question