Re: Myltiple surrogate keys in one table!

My original post was too terse.

The argument was for the use of a surrogate key BEFORE the merger is even contemplated. Take something like part number. It works perfectly well as a primary key in the current operational system. At the time that a dimension table is being designed for the warehouse, the question arises, should be use it as the primary key in the warehouse as well, or should we use a surrogate key?

If we use it as the primary key in the warehouse, we can't let it be null. Every part has to have a part number. Later on, when the merger occurs, every part from the "other company" has to be assigned one of "our company's" part numbers before it can be included in the parts dimension. This can get messy. This sounds like the solution you implemented, so you can either attest to this messiness or not.

If we use a surrogate key in the warehouse dimension table, we can now also include the part number, but we can allow it to be null. It won't be null for any of the parts we get from the existing system.

Later on, when the merger comes around, we just add a new column to the dimension table for the "other company's part number", and let it remain null for all of "our company's" parts. When the other company's parts are added to the dimension, they get their own unique surrogate keys using a mechanism similar to the one already being used for "our company's" parts.

Anticipating the merger, even when we aren't told about it, is the reason for using a surrogate key in the dimension table. There are other reasons.

--
Regards,
    David Cressey
    www.dcressey.com
"--CELKO--" <joe.celko_at_trilogy.com> wrote in message
news:7e67a7b3.0107141719.7be8d1e9_at_posting.google.com...


> >> Since both of the "original operational primary keys" allow nulls


> in the

> data warehouse, they do not qualify as candidate keys of any kind in

> that

> context nor do they qualify as component columns in a compound

> candidate

> key. Isn't it true that in this example the only candidate key is the

> new

> surrogate key?  <<

>

> I would agree.  In the merger I saw was a British and an American

> welding equipment suppliers who had designed their own inventory

> systems and catalog numbers.  There were also problems about ISO

> versus American measurement units, packaging things by 10's or 12's,

> etc.  The final solution was to set up a conversion table which would

> convert one catalog number to the other where they could.