Re: Separate foreign keys with shared ID space

Hans Forbrich <forbrich_at_yahoo.net> wrote in message news:<jZROc.2236$T_6.1453_at_edtnps89>...
> Marshall Spight wrote:
>
> > "Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message
> > news:410affcf$1_at_post.usenet.com...
> >>
> >> A PK should have no business meaning.
> >
> > Says who? Can you justify this statement?
>
> A PK should be selected to uniquely identify an entity. Ideally, and by
> formal definition, the PK is invariant.
>
> All to often a unique attribute of the entity, such as empno (or SSN/SIN or
> name or email address,) is used as the PK. Attributes generally have a
> business meaning. Such attributes can change, although some change very
> infrequently.
>
> Selecting an attribute as the PK can cause DBAs (and/or developers and/or
> businesses) headaches when attempting to change the PK. This is especially
> true when taking the offline archives into account.
>
> All of the examples are not invariant, although they tend not to change
> often. In some cases using the suggested PK is against the law (eg: SIN in
> Canada, has privacy implications).
>
> Hans

exactly.
how are you going to use SSN number as a primary key if it is properly encrypted?
use a function-based-index to decrypt it? no. that leaves the decrypted values in the index. security violation. use a surrogate key (e.g. sequence-generated value) for the pk_id and be done with it. it also leaves you free to change the encryption method used and not have to update all of the foreign keys.

now, this does not discuss the general point at all. it merely singles out the case of storing a SSN in clear text in the database.
bad design, in my opinion.

btw, the enhancements in oracle 10g for encryption look good. 56 bit DES is going away soon.

-bdbafh Received on Sun Aug 01 2004 - 19:46:51 CEST