Re: surrogate keys or composite?

  • From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
  • To: ax.mount@xxxxxxxxx
  • Date: Tue, 20 Jun 2006 08:54:43 -0500

Amonte,

Okay, is this a data warehouse appplication? You don't explicitly say
whether it is, but a lot of the terms you use sound like DW. Is it star
schema? The way you describe the Order Table and its' child make them sound
like fact tables.
   Basically this sounds like you want to take a star schema that uses
natural keys and convert it to use surrogate keys. A commendable idea. I
don't think there is any magic solution, especially if it is as large as
most DW fact tables grow to be. Obviously the dimension tables will need to
be converted to surrogate keys as well, and the users should be using the
dimension tables in their queries. Take a look at the user queries and see
how much would need to be changed.
   So I think the answer is that all data would need to be rebuilt. Since
this is a lot of work and likely requires user changes, the best idea would
be to ask the users what additional features they could use and then make
the surrogate key change a part of a "phase 2". And for data warehouses,
there is always additional data the users want.

Dennis Williams


On 6/20/06, amonte <ax.mount@xxxxxxxxx> wrote: 

Hi all

Reviewing an application over here and came across to an issue, probably
not a very big issue but have kept me thinking several days.

The database is designed to report the "workflow" about payments to
suppliers in a Big Supermarket. It collects data from at least 6
Transactional systems, i.e 6 data sources.


Considering only the order management reporting module of the database
----------------------------------------------------------------------------------------------------------------------------------------------------------

The supermarket is right now a Organization with a company so we have a
company_code
The supermarket has right now 1500 stores so we have a store_code
The supermarket operates in 4 countries so we have a country_code
We have an order_code
We have a supplier_code
We have an ordering_date

All 6 codes make up the Primary Key for the Order Table
The child table of this Order Table (Order details, line by line) is made
up of 6 codes plus the line_code

In this database there are quite a few modules which works the same way,
composite natural keys to make up the PK. There are a couple of tables whose
PK are over 10 columns.


My question is if I wanna use Surrogate Keys how can I make to the rest of
world know which key is their parent? How do I publish the Surrogate to the
rest of the tables?




Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2006