| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Data denormalisation seems some attractive
Hi Jared,
Here are more details.
One commission detail must be one of the types:
manager override (12 fields)
first year commission (15 fields),
renewal service fee (10 fields),
charge back (9 fields),
vesting payment (1 field),
subsidy (2 fields),
supplementary commission (2 fields)
sales bonus (3 fields),
deferred commission (12 fields)
I just talked to the concerned DBA, those are coming from a mainframe system and there is nothing we can do about them even if some types are strange : For example, the manager overrided type must be linked to 1 of 4 specific types. In my mind an override is not a type but just an attribute of a type, on the mainframe system it is considred as a type.
Also, it seems that they are changing some rules so I'll wait to se how my colleague is doing.
Thanks
Stephane
-----Original Message-----
From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
Sent: Tuesday, September 30, 2003 2:38 PM
To: ORACLE-L_at_fatcity.com
Cc: stephane.paquette_at_standardlife.ca
Subject: Re: Data denormalisation seems some attractive
Importance: High
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: 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).Received on Tue Sep 30 2003 - 15:24:39 CDT
 |
 |