Re: which data model is better?

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0401201435.2d1975c4_at_posting.google.com>...
> 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.

I agree, but I would rather change the table structure for adding a third address if the need arise, I don't want to spend 3-4 times effort to develop ( put aside ease of use and performance ) just to cover this 0.1% chance of change. I see equal chance to change for address and to change other customer attributes, so you still end up same possibility to change your code with the relational model. To be extremely flexible, you probably end up with one master customer table with only customer_id primary key in it and each customer attribute as two table like

customer_address address
customer_phone phone
customer_name name
customer_language language
customer_spouse spouse
customer_nationality nation

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

There are infinite methods, but this query is logically flawed, Needless to say how poor the performance will be. You'd better use outer join (which will prevent some better optimization plans to be chosen) in order not to miss some customers. Besides that, I see this query is not flexible either, you will change this view as frequently as new address or phone added.

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

Thanks for pointing the error, this is the right one ...
or '|' || home_phone || '|' || work_phone || '|' ... like '%|416%'

The point here is I want to avoid writing tedious home_phone like '416%' or work_phone like '416%'..

and try to use full scan on function index to speed it up.

ORACLE and other DBMSs have lots of
> optimizations to handle table joins.

I agree ORACLE can handle joins very nicely, and I'm not afraid to join if necessary no matter it's two tables or what ever number. I just don't do unnecessary joins.

Your single table design is not,
> automatically faster just because it is a single table.
I agree, I'll do benchmark on this later, but I'm confident single table will win.

>
> 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.
I'm not flatfile thinker, I prefer to design model that make common sense, for emp/dept, I'll split, for customer info like what I described, I would rather keep it as one, I don't see data coruption and redundant except a little bit less flexibility. Try to write DML code against "seperate table" model and try to write some query/reports on it(I'm doing it right now), you'll see why I insist on one table design.

> Ed Prochak
Received on Thu Jan 22 2004 - 15:30:09 CST