Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: which data model is better?

Re: which data model is better?

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 26 Jan 2004 11:57:52 -0800
Message-ID: <4b5394b2.0401261157.311e23f9@posting.google.com>


seaelephant_at_hotmail.com (dx) wrote in message news:<7f28ac37.0401221330.319375d3_at_posting.google.com>...
> 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.

You miss the point. First design your entities, NORMALIZE the tables, and ONLY when there is a PERFORMANCE BOTTLENECK should you consider DENORMALIZING tables for performance.

Most of the time you'll find the performance tuning are planning for doesn't buy you the gain you thought you'd get.

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

If those attributes and entities meet your requirements, the problem with the list above, at the design stage, is WHAT???
>
> >
> > > 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,

oops, yes, I left off the address_type. Just add a couple more lines of the form:
 and cust_home.address_type = 'HOME'

Note, I'm trying this with the implied table design you presented.

> Needless to say how poor the performance will be.

Again the same ASSUMPTION on your part. Performance is not apriori poor for a join over a single table. When will you accept that fact?

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

but changing a view is much easier than adding columns to a table. And I only suggested the view to emulate your flat table design. in a real application you'd add the new attributes in the application queries where appropriate.

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

huh? the error is one the city columns!

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

And got it wrong!
You are saving the tedium of writing a query in exchange for a possibly inflexible (ie, hard to maintain) table design. You are saving the wrong stuff!

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

with the proper table design you may not have to resort to function based indices.
>
> 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.

This is the proper sentiment. So why do you want to force the join by making a flat table?
>
> 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.

repeating the assumption doesn't make it true. Besure to test in a realistic manner, ie after loading the table, make sure some updates and deletes and inserts are done. (IOW, test in the potentially fragmentedd environment of real life.)

>

> 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

I've done work in both kinds of designs. Trust me the flat table model is not necessarily easier. A properly normalized model is so much easier to work with.

But this is just advice. You asked for it. You can take it or ignore it.
the choice is yours.

Ed

but I'd be happy to know who you work for so that I can get some contract work to clean up this mess later. 8^) Received on Mon Jan 26 2004 - 13:57:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US