Tony the Pony Tony the Pony -4 years ago 81
SQL Question

Best way to model Customer <--> Address

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?

Answer Source

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.)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download