Re: Data denormalisation seems some attractive

Stephane,

>From the limited information available, I will take a stab at what seems
to
be going on here.

I don't think there are really 8 entities here first of all, at least not for the
details given. No mention is made of the person being paid the commission,
who the sale was too, the items sold, etc.

It would appear that there is a commission entity. When it comes to commission
types though, I don't understand why there are 7 entities.

What if a new commission type appears? Modify the model? This doesn't sound like a flexible solution. Please don't say it will never change: that statement
has been rendered false too many times. :)

Since I don't know what differentiates one type of commission from another, it's
a little difficult to say just how to proceed from here.

Keeping it simple, a single entity with sufficient attributes, some nullable, to
capture all needed commission type info.

I thought about mocking up an example for this exercise, but it would be much
easier and more productive if you could provide some relevant details on the
commission types, with an explanation of why it's thought that separate entities
are needed for each.

Jared

"Stephane Paquette" <stephane.paquette_at_standardlife.ca> Sent by: ml-errors_at_fatcity.com
 09/30/2003 07:44 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Data denormalisation seems some attractive

Hi,

A co-worker of mine is working on a tiny project. Let say you have commission info and commission details, there are 7 types of commissions.
The 7 types of commission shares common fields (from 100% to 30%)

>From a conceptual point of view, you have 1 entity that is the commission
info and 7 entities for the seven types of commissions since they all have private info (some fields are mandatory).. 1 commission must be 1 of the 7 types.

Now at the physical level, info is write once, never update and read through
a selective search criteria (agent number). Volume is about 8 000 000 commissions.

You can have the physical model as the conceptual model. That means you do not have any work for managing integrity but when reading
you have more work to get the data.
Or
You can put all data in 1 table with all fields. When data is inserted you must managed integrity (some common fields are mandatory for 1 type of commission but not for another one) but reading is fast just 1 record to read.

Since, I do not know what the future of this project is I recommended the other DBA to keep data normalized.
And to do a benchmark if he really wants to denormalize.

Your opinions please.

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187 stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: stephane.paquette_at_standardlife.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).