RE: ODS and data modeling

MessageThanks,

Using one code table is easier to manage from a development point of view but it prevents using referential integrity in the database, that's my main concern.

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

  -----Original Message-----
  From: Tierstein, Leslie [mailto:LTierstein_at_cns.gov]   Sent: Thursday, July 03, 2003 11:42 AM   To: 'ORACLE-L_at_fatcity.com'
  Cc: 'stephane.paquette_at_standardlife.ca'   Subject: RE: ODS and data modeling

  Handling codes in one table via separate tables has been extensively discussed in development-oriented lists. The consensus falls heavily on having one single table -- it is much easier to manage. For example, you only need one fairly simple maintenance form to allow users to maintain/view the code values.

  Sample DESC for one physical table which holds multiple logical code tables:

  SQL> desc cn_codes

   Name                                          Null?    Type
   ----------------------------------------- -------- ---------------
   CODE_ID                                      NOT NULL NUMBER(10)
   ACTIVE_IND                                   NOT NULL VARCHAR2(1)
   CODE_SHORT_DESC_TXT                          NOT NULL VARCHAR2(70)
   CODE_TABLE_NM                                NOT NULL VARCHAR2(30)  --
logical code table name
   CODE_VALUE_CD                                NOT NULL VARCHAR2(15)  --
logical code value
   CREATE_DT                                    NOT NULL DATE
   CREATE_USER_NM                               NOT NULL VARCHAR2(12)
   MOD_DT                                       NOT NULL DATE
   MOD_USER_NM                                  NOT NULL VARCHAR2(12)
   CODE_LONG_DESC_TXT                                    VARCHAR2(1000)
   TABLE_SPECIFIC_TXT                                    VARCHAR2(40)
   DISPLAY_SEQ_NBR                                       NUMBER(3)

  Couple of notes on the above:
(1) Above table definition includes artificial, sequence-generated PK,
which is recommeded for DW or ODS, given issues with changing dimensions.
(2) Don't know if you will want just an Active indicator or a range of
dates in which the particular code is active.
(3) OLTP system needs additional information about codes, stored in
table_specific_txt; might not be required in ODS/DW environment.
(4) Specific definition of table_specific_txt, and a definitive list of
all the code tables is contained in another logical table, which could be another code table or stored elsewhere.
(5) If required, very easy to create views on top of the above, to hide
the physical table from developers.

    -----Original Message-----
    From: Stephane Paquette [mailto:stephane.paquette_at_standardlife.ca]     Sent: Wednesday, July 02, 2003 4:56 PM     To: Multiple recipients of list ORACLE-L     Subject: ODS and data modeling

    Hi all,

    I'm doing the data model for an Operational Data Store. The ODS will serve to consolidate data from many operational systems and mainly from a new ERP, then most of the data will go in an existing data warehouse.

    I've worked with datawarehouses before but never with ODS.

    I've check about Bill Inmon and at IBM red book site on the web.     Any other good site on ODS ?

    Also, what are your arguments when choosing between 1 table handling all codes or having a table for each code.

    TIA     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

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