Best way to model Customer <--> Address

Posted by Jen on Stack Overflow See other posts from Stack Overflow or by Jen
Published on 2009-03-15T20:14:44Z Indexed on 2010/06/08 7:02 UTC
Read the original article Hit count: 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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about database-design