Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

Am Sonntag, 25. August 2013 14:23:44 UTC+2 schrieb Wolfgang Keller:

>
>
> > In our field of work unique surrogate keys never violate referential
>
> > integrity.
>
>
>
> They inevitably will, depending on the database schema, as
>
> robur.6_at_gmail.com has explained. As soon as you have overlapping
>
> foreign key relationships. Which you will inevitably get sooner or
>
> later during the evolution of your data model, according to Murphy's
>
> law.

When mixing (external) identification and (refential integrity then you are right. We strictly separat external and internal identity. Only internal identity is used for referential integrity. Try tracability with "natural keys" when moving a subsystem e.g. "TY12" with all its components to "TZ12" and it should be "TX12". Changing parts of a plant design is bread and butter for our company
>
>
>
> GUIDs (which are "unique" only within the virtual IT "world" and have
>
> absolutely no identification function in the real world) *must* fail
>
> in this case.
>
In our field of work we have to deal with function (User Identity), actual hardware (serial number) and internal identity (planung inspections and tests to ordered components with currently unknown serial number). Linking hardware to function with guids is a simple way to identify unknown hardware with functional ident. At end the serial number is added as documentation.

>
> > The advantage of surrogate keys is, they will be created, but never
>
> > changed.
>
>
>
> They will. See the "information logistics" chain over the lifespan of
>
> those physical items.
>
>
Explain !

>
> Or do you want to create a world where everything in the "real world"
>
> is identified by its "SAP number" (or "personal IDs" in Sweden and
>
> Norway)? I know the mess that results from that requirement in
>
> companies that use SAP. The users who have to deal with that *hate*
>
> it.
>
No! Mixing external identification, uniqueness and linking between entities might work, but not for our applications
>
>

>
> In the technical world, a serialised component never changes its serial
>
> number during its entire lifespan. In fact this would be a crime in any
>
> half-civilised country, since it makes tracability impossible. Which is
>
> a legal requirement for safety-critical components.
>
That is not the point. In powerplant stations components never been identified by serial number but the intended function. The same valve, pump or motor could serve different functions and has different idents accordingly. Deinstallation and reinstallation yields to a changed installtion.

m.f.G
Karl Scheurer Received on Mon Aug 26 2013 - 14:45:15 CEST