Re: Myltiple surrogate keys in one table!

Another interesting application would be for maintaining UNITY DIMENSIONS. One could argue surrogate keys for individual components, as well as overall UNITY surrogate.
-Aakash

"David Cressey" <david_at_dcressey.com> wrote in message news:dpa47.38$x4.2280_at_petpeeve.ziplink.net...
> 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.
>
>
Received on Fri Jul 20 2001 - 22:58:43 CEST