Oracle-L: Design question, historic and views

From: Stephane Paquette <stephane.paquette_at_standardlife.ca>
Date: Tue, 19 Aug 2003 07:34:25 -0800
Message-ID: <F001.005CB88F.20030819073425@fatcity.com>

Hi,

In an ODS, it is feed in near real time during the day and pushes data to the DW and other systems at night.
So it may need to keep several copies of the same data (several changes or the DW can be offline so the the ODS may need to keep several days).

All tables have a surrogated PK and the PK from the source systems are used as alternate non-unique keys.
All tables will have a DW_transfer indicator (yes/no) There are 2 designs I can immediately think of :

I keep all the data in the same table and I use fields like last_version_indicator (yes/no), start date, end date. This is fast since it implies inserting the new record and updating the previous record.
The last version for each rows are kept in a table and the historic are kept in a second table.Each table has its historic table. This implies inserting in the core table, insert the previous record in the historic table and deleting it.
another way ?

The ODS must answer the following:
All changes need to be transferred to the DW not only the last version. End-users will query the ODS (I do not know yet if queries will access only the last version or not).

Views.
In the case the solution1 is used. Would you used views to do the following table_t : the table
table_last_version_v : view showing only the last version (with where last_version_indicator='YES'), used for queries.

In both cases, would you used a view
table_not_transferred_v : view showing records with the DW_transfer_indicator set to no, that view would be updated by the ETL processes

or let the the programs do the where DW_transfer_indicator=no.

Thanks

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). Received on Tue Aug 19 2003 - 10:34:25 CDT