Re: which data model is better?

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%' ...

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.

-- 
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)