Re: Separate foreign keys with shared ID space

Paul Drake wrote:

> 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

Paul,

I'm not quite sure what you are saying.

I advocate using PK that is defined as an invariant. I also point out that many default choices for PK are bad as they are not invariant. Quite honestly, I do not care whether a surrogate is chosen, just whether it is invariant.

I also mention that some seemingly obvious choices are bad due to [local] legal constraints. For exmple, in Canada it does not matter whether you encrypt the SIN or not. The SIN can only be used for tax reporting purposes and may not be stored for any other purpose, encrypted or otherwise.

/Hans Received on Sun Aug 01 2004 - 23:53:31 CEST