Best Way to Model Customer <--> Address

Best way to model Customer -- Address

I tend towards first approach for all the usual reasons of normalisation. This approach also makes it easier to perform data cleansing on mailing details.

If you are possibly going to allow multiple addresses (mail, residential, etc) or wish to be able to use effective dates, consider this approach

   Customer   (id, phys_address_id)
   Cust_address_type (cust_id, mail_address_id, address_type, start_date, end_date)
   Address    (id, street, city, etc.)

How to model Address relationship with Customer and business ?

1. Use the party model. A party is an abstract organization or individual. Use Table Inheritance to model this.

2. Use a junction table between MailingAddress and Party.

Using Single Table Inheritance:

create table party (
  party_id int primary key,
  type varchar(20) not null, --this should be a char(1) or smallint pointing to a lookup table
  name varchar(255) not null
);

insert into party values 
(1, 'Organization', 'Acme, Inc'),
(2, 'Individual', 'John Doe');

create table mailing_address (
  address_id int primary key,
  address varchar(255) not null --add other address fields
);

insert into mailing_address values
(1, '123 Wall Street...'),
(2, '456 Main Street...');

create table party_mailing_address (
  party_id int,
  mailing_address_id int,

  primary key (party_id, mailing_address_id),

  foreign key (party_id) references party(party_id),
  foreign key (mailing_address_id) references mailing_address(address_id)
);

--assign addresses to the parties:
insert into party_mailing_address values
(1,1),
(2,2);

How to best represent addresses in a database

A DBA where I used to work told me this gem, and it's worked great for us (the first two steps are the same as in your solution):

1. Remove AddressLine1, AddressLine2, City, StateOrProvince, PostalCode from Customers, Contacts, Facilities and Clients.
2. Create AddressTypes table with fields AddressTypeID, AddressTypeName, AddressTypeDescription, AddressTypeActive, LastUpdateUser, LastUpdateTime
3. Create Addresses table with fields AddressID(PK), AddressTypeID(FK), AddressLine1, AddressLine2, City, StateOrProvince, PostalCode , LastUpdateUser, LastUpdateTime, CustomerID(FK), ClientID(FK), ContactID(FK), FacilityID(FK)
4. On the addresses table, set up a constraint so that only one of the CustomerID, ClientID, ContactID, or FacilityID foreign keys may be non-NULL at a time.

This way you've got all your addresses in one table, they can reference any record you need, your referential integrity is intact, and you don't have in intermediate table that you have to traverse.

The downside is that if you want to add addresses to a new class of object (e.g. an Employee table), you have to add a EmployeeID column to the Addresses table, but that's pretty easy.

Best strategy for storing order's addresses

Personally I would model the addresses as a single table, every update to the address would generate a new row, this would be marked as the current address.

I guess you could allow deletes if there are no related orders, however it would be simpiler to mark the old record as inactive.

This will allow you to preserve the relationship between orders & addresses,
and to easily query the historic data at a later date.

see the wikipedia entry for slowly changing dimensions

SQL Database Design Best Practice (Addresses)

You're on the right track by breaking address out into its own table. I'd add a couple of additional suggestions.

1. Consider taking the Address FK columns out of the Customers/Orders tables and creating junction tables instead. In other words, treat Customers/Addresses and Orders/Addresses as many-to-many relationships in your design now so you can easily support multiple addresses in the future. Yes, this means introducing more tables and joins, but the flexibility you gain is well worth the effort.

2. Consider creating lookup tables for city, state and country entities. The city/state/country columns of the address table then consist of FKs pointing to these lookup tables. This allows you to guarantee consistent spellings across all addresses and gives you a place to store additional metadata (e.g., city population) if needed in the future.

Entity Framework Code First: How can I model a Customer / Address relationship?

The problem is... which gets deleted first, the customer, or the mailing address? You can't delete them both at the same time, the deletes will happen in a sequence. When the first gets deleted, it fails the rule b/c the second hasn't been deleted yet.

From what I can see of your model, I'd not use the foreign keys to handle this logic, I'd handle it during object validation by putting a [Required] attribute on the MailingAddress property instead of the foreignkey.

You should also consider additional implementation logic to ensure that the MailingAddress is part of the Addresses collection.

---

Related Topics