Re: which data model is better?

From: dx <seaelephant_at_hotmail.com>
Date: 17 Jan 2004 20:06:42 -0800
Message-ID: <7f28ac37.0401172006.321fc763@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1074380978.794638_at_yasure>...
> dx wrote:
>
> <snipped>
>
> > 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.
>
> I agree not to stick rigidly with it but reconsider your attempt to
> support a single table model. Change your request to ... give me all
> customers whose city is 'TORONTO' and that have a '416' phone number.
>
> With your model you get something like this:
>
> SELECT ...
> FROM ...
> WHERE (home_city = 'TORONTO' OR work_city = 'TORONTO' OR sec_add_city =
> 'TORONTO' or ...
> AND (home_phone lIKE '416%' OR work_phone LIKE '416% OR cell_phone LIKE
> '416% OR fax_phone LIKE '416%' ...

I might do like this if it's a real business question: select ...
from
where home_city || '|' || work_city || '|' ... like 'TORONTO' or home_phone || '|' || work_phone || '|' ... like '416%';

The point is address and phone we need to capture are predictable, people can have infinite other address and phone numbers, but they're just meanless to our business. If the case is dept/emp or student/course or emp/project, I definitely go for separate tables, but for customer, I still think one table make more sense.

If I say put each customer information in one row ( it makes sense for this requirement, but definitely not for put dept info and all its emp info in one line), and each customer has 2 address and 4 phone numbers, how you want to do that? like the following or you have better to show me.

select
...

max(decode(customer_address.address_type_cd, 'HOME', address.line1,
null)) home_address_line1,
max(decode(customer_address.address_type_cd, 'HOME', address.line2,
null)) home_address_line2,

...
from
customer, customer_address, customer_phone, address, phone where phone.phone_number like '416%' and address.city = 'TORONTO' and customer.customer_id = customer_address.customer_id and customer_address.address_id = address.address_id and customer.customer_id = customer_phone.customer_id and customer_phone.phone_id = phone.phone_id group by customer.customer_id;

Do you think the performance of this query will outperform "
select ...
from
where home_city || '|' || work_city || '|' ... like 'TORONTO' or home_phone || '|' || work_phone || '|' ... like '416%'; "

>
> With a relational model you'd just have to query a single address and
> single phone column. The relational model still works if you add another
> phone type. Your query must be rewritten.
>
> Your example works only because it is contrived. The relational model
> works for all queries. Date and Codd understood the advantages of a
> relational model over a flat file. Yet you seem convinced that the old
> COBOL flat file databases were better. I don't agree. Which doesn't mean
> there are times to not normalize. I just don't think this is one of them.
Received on Sat Jan 17 2004 - 22:06:42 CST