which data model is better?

From: dx <seaelephant_at_hotmail.com>
Date: 16 Jan 2004 13:36:33 -0800
Message-ID: <7f28ac37.0401161336.627f277e@posting.google.com>

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 Received on Fri Jan 16 2004 - 15:36:33 CST