Best way to model Customer <--> Address

Asked 2009-03-15T20:14:44.187

Every Customer has a physical address and an optional mailing address. What is your preferred way to model this?

Option 1. Customer has foreign key to Address

   Customer   (id, phys_address_id, mail_address_id)
   Address    (id, street, city, etc.)

Option 2. Customer has one-to-many relationship to Address, which contains a field to describe the address type

   Customer   (id)
   Address    (id, customer_id, address_type, street, city, etc.)

Option 3. Address information is de-normalized and stored in Customer

   Customer   (id, phys_street, phys_city, etc. mail_street, mail_city, etc.)

One of my overriding goals is to simplify the object-relational mappings, so I'm leaning towards the first approach. What are your thoughts?

Edit Report

2 Answers

I'd prefer #1. Good normalization and communicates intent clearly. This model also allows the same address object (row) to be used for both addresses, something I have found to be quite valuable. It's far too easy to get lost in duplicating this information too much.

answered 2009-03-15T20:23:11.403

Option 3 is too restrictive, and option 1 cannot be extended to allow for other address types without changing the schema. Option 2 is clearly the most flexible and therefore the best choice.

answered 2009-03-15T21:04:12.600

Best way to model Customer <--> Address

2 Answers

Your Answer