RE: ODS and data modeling

[Tierstein, Leslie]

Title: Message

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:[EMAIL PROTECTED]
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

[EMAIL PROTECTED]