Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: surrogate keys or composite?

Re: surrogate keys or composite?

From: Dennis Williams <>
Date: Tue, 20 Jun 2006 08:54:43 -0500
Message-ID: <>


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 <> 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?

Received on Tue Jun 20 2006 - 08:54:43 CDT

Original text of this message