Re: which data model is better?

seaelephant_at_hotmail.com (dx) wrote in message news:<7f28ac37.0401172006.321fc763_at_posting.google.com>... []
>
> The point is address and phone we need to capture are predictable,

99.9% of the time is the figure you mentioned in a previous post. IOW, there is still a chance of having this requirement change sometime in the future.

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

maybe it does, but your arguement for using one table is weak. And one major point of having a DBMS versus a flat file system is hiding the real structure. I can take the multiple tables and create a view that LOOKS to your application like a single table with all those fields. But when it come time to make a change in the structure, the single table case will have problems while the view will continue.
>
> 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;
>

This is what report writers are for. Besides the above is a contrived version. I'd do it like this:
select home.line1, ...

       work.line1, ...
FROM customer, customer_address cust_home, customer_phone, address home,

      customer_address cust_work, address work,     ...
WHERE
     customer.id = cust_home.cust_id
 and cust_home.address_id = home.address_id  and customer.id = cust_work.cust_id
 and cust_work.address_id = work.address_id ...

NO DECODE() function calls needed.

> 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%';
> "
>

(ignoring the fact that this query doesn't even work, the table join may well perform better. ORACLE and other DBMSs have lots of optimizations to handle table joins. Your single table design is not, automatically faster just because it is a single table.

Stop being a flat-earther. open your mind to some sensible suggestions.
Learn the theory before trying to justify a weak position.

I sincerely hope this helps you learn.

   Ed Prochak Received on Tue Jan 20 2004 - 16:35:32 CST