Re: which data model is better?

dx wrote:

> There is an argument inside our company regarding how to design data
> model to store customer information like
>
> salutation
> first_name
> middle_name
> last_name
> home_address_line_1
> home_address_line_2
> home_city
> home_province
> home_postal_cd
> mailing_address_line_1
> mailing_address_line_2
> mailing_city
> mailing_province
> mailing_postal_cd
> home_phone
> business_phone
>
> One opinion is to design one table to hold all information. The other
> opinion is to design like this,
>
> CUSTOMER table
> customer_id(PK)
> customer_solutation
> first_name
> middle_name
> last_name
>
> CUSTOMER_ADDRESS table
> customer_id(PK)
> address_id(PK)
> address_type_cd(PK)
> start_dt(PK)
> end_dt
>
> ADDRESS table
> address_id(PK)
> address_line_1
> address_line_2
> city
> province
> postal_cd
>
> CUSTOMER_PHONE table
> customer_id(PK)
> phone_id(PK)
> phone_type_cd(PK)
> start_dt(PK)
> end_dt
>
> PHONE table
> phone_id(PK)
> phone_number
>
> The reason is this model is more flexible and easy to maintain, such
> as if later another address like business address or phone number like
> cell phone need to be captured, we don't need to alter table
> structure; It also supports history information of address and phone.
> They also argue that the first design even doesn't meet the 1NF
> criteria, they claim the second is the 3NF.
>
>
> What's your opinion on this?
>
> Thanks,
> Daniel

In a relational database one should follow Cobb and Date unless there is a compelling reason not to.

Therefore ... a separate address and phone table. Consider the implications to the non-relational model of needing a place for a third address or for FAX and cell phone numbers. The data model keeps changing, the code keeps being rewritten, everything requires retesting.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)