Re: primary key as subtype discriminator

On Aug 31, 9:51 am, philiptaylo..._at_yahoo.com wrote:
> Hello,
>
> I have something like:
>
> TABLE A
> a PK
>
> TABLE B
> b PK
>
> TABLE AB (many-to-many table)
> a, b PK
>
> TABLE AB-1 (AB subtype)
> a, b PK
>
> TABLE AB-2 (AB subtype)
> a, b PK
>
> Can I use the attribute "a" (part of PK) as subtype discriminator?
>
> Thanks,
> philip

Hi, Philip.

I think that's a bad idea. I know this may sound a bit shop-worn, but all subtypes should be mutually exclusive and exhaustive. Further, they should each describe a semantically unique "kind of" the supertype, with each subtype described by attributes unique to it.

Taking these two guidelines together, this suggests (to me, anyway) that if all subtypes are known, then all instances of the domain of any category discriminator of these subtypes are also to be known.

Inspecting the value of the PK to decide which subtype to navigate to also smacks of "smart" keys, which gets us to the whole natural key vs. smart key vs. surrogate key holy war. Besides, if the PK is, say, a 4-byte unsigned integer, are you saying you'd have (in order to satisfy the "exhaustive" property) 4,294,967,296 subtypes?

Now, if you wanted to add a category discriminator to your AB supertype and make it part of a three part composite key, that's possible, assuming you're enforcing a business rule that mandates that you may only have one instance of each subtype.

Perhaps you can elaborate a bit more on what you're after, and we can humbly suggest better approaches?

Regards,

--Jeff Received on Wed Sep 03 2008 - 02:37:16 CEST