RE: Data denormalisation seems some attractive

So, you're not allowed to actually model this at all, but just port over some VSAM structures and call it a database.

See the thread on COBOL.

Jared

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

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        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-----
Sent: Tuesday, September 30, 2003 2:38 PM To: ORACLE-L_at_fatcity.com
Cc: stephane.paquette_at_standardlife.ca
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: 
  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).