Re: Database design, Keys and some other things

"vldm10" <vldm10_at_yahoo.com> wrote in message
> This story about "external" and "internal" is interesting. I
> would like to add some practical thoughts related to my solution, to
> clarify it. I connected "internal" and "external" using
> following identifiers:
> 1) The identifier of the state of an entity.
> 2) The identifier of the entity.

This connection works in your particular case. But not all internal identifiers (surrogate keys) are associated with a state of an entity. Here's an example:

Alpha Airlines merges with Beta Airlines to from a new entity, Gamma Airlines. The employees win the right to carry over their employment record, and benefits that go with time of service. It's desired to create a data warehouse with the combined employment history of both sets of employees.

Alpha Airlines has identified its employees by employee_id starting at 1 and assigned sequentially.
Beta Airlines has identified its employees by employee_id starting at 1 and assigned sequentially.

Clearly, employee_id is not going to work as the key in the DW. So we invent a surrogate for DW purposes, and use copies of the surrogate as foreign keys elsewhere in the ware house. We have a couple of columns, ALPHA_EMPLOYEE_ID and BETA_EMPLOYEE_ID in the employee table, and for every employee that came over with the merger, one of these columns is filled in, and the other one is missing.

This is a different reason to go with surrogates than the one that pertains to the case you raised.

Your surrogate really stands as a surrogate for a compound key: the key of the entity, plus the key of a time span that is bounded by two state transitions of the entity in question. So it really doesn't act as a surrogate for only the entity. Received on Wed Oct 05 2005 - 15:16:32 CEST