Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Table design problem

Re: Table design problem

From: Mike Sherrill <MSherrill_at_compuserve.com>
Date: Wed, 07 May 2003 12:11:36 -0400
Message-ID: <1f3ibvgvdrica2gm80b5oeha6cgn5j7hsq@4ax.com>


On Tue, 6 May 2003 11:11:48 +0000 (UTC), "Roy Hann" <rhann_at_globalnet.co.uk> wrote:

>Anyway, our names happen to
>correspond with the terms used by the business and give us a common language
>for communicating with the users. There is no value in changing the names
>to solve a problem we don't have.

Given that, to you, "customers" doesn't mean "a party who has bought or ordered something from another party", I'd be fascinated to hear your definition. I'm serious; many of us (database designers, not necessarily database theorists) collect odd little things like that for our own education.

[snip]
>Turning it around, what would be the disadvantage of encoding the
>discriminator in the primary key? I can't see any, and I can see a couple
>of advantages to doing so.

>First of all I could never create a projection
>of the table that preserves the key but lacks the discriminator.

Are you sure about that?

>Secondly,
>if my subtypes have further subtypes (e.g. non-profit corporations are a
>subtype of corporations, which are a subtype of parties), then I can tell
>without reference to the supertype table what further subtypes I should
>expect based on an inspection of a row in any given subtype.

I suspect I don't understand you here.

Let's say we start with a type hierarchy like this, and assume that there are some rows in all the tables.

 Base tables
 --
 Parties
   Corporations

     Non_Profits
     For_Profits

   Persons

I inspect a row in the base table Corporations. I can tell there are two subtypes of corporations, because Corporations includes a column that discriminates non-profit corporations from for-profit corporations. I can tell there are two subtypes of corporations without referring to the supertype Parties. This seems to be true regardless of whether the column that discriminates non-profit corporations from for-profit corporations is part of the key.

I inspect a row in the base table Non_Profits. I can't tell what other subtypes of corporations there are unless I look "up" at Corporations. I can't avoid referring to the supertype. This, too, seems to be true regardless of whether the column that discriminates non-profit corporations from for-profit corporations is part of the key.

Are we saying the same thing here? I can't tell for sure.

>Thirdly I
>could add a new subtype that further specializes one of my existing subtypes
>without having to add a column to it, and more constraints.

I suspect I don't understand what you said.

In the general case, adding a subtype involves creating a table for its unique attributes, inserting a new discriminator into the supertype, adjusting the constraints, and creating a view. If this is the first subtype, the supertype usually lacks a discriminating column, so you have to provide one. If this isn't the first subtype, the supertype already has a discriminating column, and you just need to add a value that identifies the new subtype to its domain.

None of these steps seem to care whether the supertypes discriminator is part of its key.

Is that how you understand the process works?

-- 
Mike Sherrill
Information Management Systems
Received on Wed May 07 2003 - 11:11:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US