Re: which data model is better?

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1074291845.571279_at_yasure>...
> 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.

Hi Daniel,

Thanks for your comment. But, actually for our application, in 99.99% there is no need for third address, I'm pretty sure there is more chance to add other attributes like spoken_language, birth_date, etc than to add a third address. So even you choose a separate address and phone table, the seperate table model still has almost the same chance to change with one table model.

In my opinion, performance and ease of use is always my goal to design data model. Suppose a simple business question like getting all information of customers who live in city 'TORONTO' and business phone like '416%', it's always much harder to get answer from seperate table model than

select * from customer where home_city = 'TORONTO' and business_phone like '416%';

And how many extra logic I/O will be performed for seperate table model? you really want to trade performance and ease of use for this imagined flexibility?

Yes, I would follow CODD's rule, but I don't want to rigidly stick to it. Received on Sat Jan 17 2004 - 16:35:17 CST