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-----
Sent: Wednesday, July 02, 2003 4:56 PM
To: Multiple recipients of list ORACLE-L

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 <mailto:stephane.paquette_at_standardlife.ca>  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tierstein, Leslie
  INET: LTierstein_at_cns.gov

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