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

Best way to model Customer <--> Address


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

Option 1.
has foreign key to

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

Option 2.
has one-to-many relationship to
, 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 (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