Separate foreign keys with shared ID space

[Robert Brown]

Let's say I have a type hierarchy: (just an example)

the general entity customer:

CREATE TABLE customer(custom er_id int, customer_name varchar(250),
customer_type int)

three specific customer subtypes: 1 - business, 2 - home, 3 -
university

represented by the following three tables (being subtypes, they share
ID space for customer_id)

CREATE TABLE business_custom er(customer_id int, business_sector int,
.... )
CREATE TABLE home_customer(c ustomer_id int, household_incom e_bracket
int, ...)
CREATE TABLE university_cust omer(customer_i d int, number_students int,
....)

Is it possible to make a foreing key constraint that says:

the customer_id in busineness_cust omer table must reference only those
customer_id in the customer table where customer_type == 1?

the customer_id in home_customer table must reference only those
customer_id in the customer table where customer_type == 2?


Thanks!

- Robert

[Christian Antognini]

Re: Separate foreign keys with shared ID space

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert
[color=blue]
> Is it possible to make a foreing key constraint that says:
>
> the customer_id in busineness_cust omer table must reference only those
> customer_id in the customer table where customer_type == 1?
>
> the customer_id in home_customer table must reference only those
> customer_id in the customer table where customer_type == 2?[/color]

With a foreign key you can only reference a primary key. Since customer_type
is not part of it, you cannot use it on the subtypes.

On the other side, if you add customer_type to the customer's primary key,
you should add it on the subtype as well. But the foreign key alone will not
be enough, i.e. you should add a check constraint on the subtype to ensure
such a rule.

==> There is no good (from a design point of view) foreign key to enforce
such a constraint.


Chris



-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***

http://www.usenet.com

Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Lennart Jonsson]

Re: Separate foreign keys with shared ID space

robertbrown1971 @yahoo.com (Robert Brown) wrote in message news:<240a4d09. 0407271513.43df ecc7@posting.go ogle.com>...[color=blue]
> Let's say I have a type hierarchy: (just an example)
>
> the general entity customer:
>
> CREATE TABLE customer(custom er_id int, customer_name varchar(250),
> customer_type int)
>
> three specific customer subtypes: 1 - business, 2 - home, 3 -
> university
>
> represented by the following three tables (being subtypes, they share
> ID space for customer_id)
>
> CREATE TABLE business_custom er(customer_id int, business_sector int,
> ... )
> CREATE TABLE home_customer(c ustomer_id int, household_incom e_bracket
> int, ...)
> CREATE TABLE university_cust omer(customer_i d int, number_students int,
> ...)
>
> Is it possible to make a foreing key constraint that says:
>
> the customer_id in busineness_cust omer table must reference only those
> customer_id in the customer table where customer_type == 1?
>
> the customer_id in home_customer table must reference only those
> customer_id in the customer table where customer_type == 2?
>
>
> Thanks!
>
> - Robert[/color]

Something like:

CREATE TABLE customer (
customer_id int not null primary key,
customer_name varchar(250) not null,
customer_type int not null check (customer_type in (1,2,3)),
unique (customer_id, customer_type )
);

CREATE TABLE business_custom er (
customer_id int not null primary key,
customer_type int not null default 1 check (customer_type = 1),
...
foreign key (customer_id, customer_type)
references customer (customer_id, customer_type)
);

etc.

HTH
/Lennart

[Robert Brown]

Re: Separate foreign keys with shared ID space

It seems like type hierarchies are a common technique in relation
design. What approaches do people use to provide relational integrity
in cases like this? Should I add the customer_type column to the
subtypes and then use composite foreign key? I guess that could work
but it has the downside of using up tablespace with a column of
customer_type that will always have the same value for the subtype. Is
that considered just the cost of doing business in this situation?


"Christian Antognini" <christian.anto gnini@trivadis. com> wrote in message news:<410722dd$ 1@post.usenet.c om>...[color=blue]
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Robert
>[color=green]
> > Is it possible to make a foreing key constraint that says:
> >
> > the customer_id in busineness_cust omer table must reference only those
> > customer_id in the customer table where customer_type == 1?
> >
> > the customer_id in home_customer table must reference only those
> > customer_id in the customer table where customer_type == 2?[/color]
>
> With a foreign key you can only reference a primary key. Since customer_type
> is not part of it, you cannot use it on the subtypes.
>
> On the other side, if you add customer_type to the customer's primary key,
> you should add it on the subtype as well. But the foreign key alone will not
> be enough, i.e. you should add a check constraint on the subtype to ensure
> such a rule.
>
> ==> There is no good (from a design point of view) foreign key to enforce
> such a constraint.
>
>
> Chris
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=[/color]

[John Hurley]

Re: Separate foreign keys with shared ID space

Your design may need some more consideration.

From a theoreticaly relational design standpoint, when you have a
entity type that has subtypes, at the ERD stage, you have to figure
out the attributes that belong to the (supertype), attributes that
belong to each subtype, and optionality of each of those attributes.

When you are ready to consider creating tables from those entities ...
you have a choice.

You can either create one table the supertype and in that table it
will have all of the columns that belong to the supertype plus all of
the columns that belong TO EACH of the subtypes.

OR

You do not create the supertype table and then you create a separate
set of tables for each subtype. In each subtype go all of the columns
from the supertype along with the specific set of columns that belong
to the subtype.

Then there are programming tradeoff's that depend on which way the
design tradeoff was implemented.

It appears to me if I understand what you presented, you have both the
supertype table along with the subtype tables. That looks wrong to
me.

[Carlos]

Re: Separate foreign keys with shared ID space

We have a similar design issue in our DB.

We implemented this kind of super-class/sub-class design using views.

We created views with joins with the parent & child tables and
'instead of' triggers. The users can only see the views though they
see them as if they were tables (by using synonyms).

In your example you could create three views: business_custom er_vw,
home_customer_v w, university_cust omer_vw. They share the common
attributes from
the customer table and the respective specific attributes from the
business_custom er, home_customer, university_cust omer tables.
Optionally, you can create synonyms business_custom er, home_customer,
university_cust omer for the views.

The underlying tables data are managed by the instead of triggers.

I can only say It works for us...

Hope this helps.

Carlos.

lennart@kommuni cera.umea.se (Lennart Jonsson) wrote in message news:<6dae7e65. 0407280534.7593 6feb@posting.go ogle.com>...[color=blue]
> robertbrown1971 @yahoo.com (Robert Brown) wrote in message news:<240a4d09. 0407271513.43df ecc7@posting.go ogle.com>...[color=green]
> > Let's say I have a type hierarchy: (just an example)
> >
> > the general entity customer:
> >
> > CREATE TABLE customer(custom er_id int, customer_name varchar(250),
> > customer_type int)
> >
> > three specific customer subtypes: 1 - business, 2 - home, 3 -
> > university
> >
> > represented by the following three tables (being subtypes, they share
> > ID space for customer_id)
> >
> > CREATE TABLE business_custom er(customer_id int, business_sector int,
> > ... )
> > CREATE TABLE home_customer(c ustomer_id int, household_incom e_bracket
> > int, ...)
> > CREATE TABLE university_cust omer(customer_i d int, number_students int,
> > ...)
> >
> > Is it possible to make a foreing key constraint that says:
> >
> > the customer_id in busineness_cust omer table must reference only those
> > customer_id in the customer table where customer_type == 1?
> >
> > the customer_id in home_customer table must reference only those
> > customer_id in the customer table where customer_type == 2?
> >
> >
> > Thanks!
> >
> > - Robert[/color]
>
> Something like:
>
> CREATE TABLE customer (
> customer_id int not null primary key,
> customer_name varchar(250) not null,
> customer_type int not null check (customer_type in (1,2,3)),
> unique (customer_id, customer_type )
> );
>
> CREATE TABLE business_custom er (
> customer_id int not null primary key,
> customer_type int not null default 1 check (customer_type = 1),
> ...
> foreign key (customer_id, customer_type)
> references customer (customer_id, customer_type)
> );
>
> etc.
>
> HTH
> /Lennart[/color]

[Christian Antognini]

Re: Separate foreign keys with shared ID space

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert
[color=blue]
> It seems like type hierarchies are a common technique in relation
> design. What approaches do people use to provide relational integrity
> in cases like this? Should I add the customer_type column to the
> subtypes and then use composite foreign key?[/color]

A PK should have no business meaning. Therefore, I'll not add such a column
to the primary key.

Once I solved such a problem with as many parent tables as child tables and
then by putting a views that aggregated the information over the different
types of customer. Of course it is only a good solution if you rarely query
all customers at the same time. Another drawback is that the primary key
cannot be guaranteed for the whole customers. But, if you generate the
customer id with a sequence, this should not be a problem.

Chris



-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***

http://www.usenet.com

Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Marshall Spight]

Re: Separate foreign keys with shared ID space

"Christian Antognini" <christian.anto gnini@trivadis. com> wrote in message news:410affcf$1 @post.usenet.co m...[color=blue]
>
> A PK should have no business meaning.[/color]

Says who? Can you justify this statement?


Marshall

[Christopher Browne]

Re: Separate foreign keys with shared ID space

After a long battle with technology, "Marshall Spight" <mspight@dnai.c om>, an earthling, wrote:[color=blue]
> "Christian Antognini" <christian.anto gnini@trivadis. com> wrote in message news:410affcf$1 @post.usenet.co m...[color=green]
>>
>> A PK should have no business meaning.[/color]
>
> Says who? Can you justify this statement?[/color]

A good reason for this is that business meanings can change, but
primary keys can't.

A typical example of this is the use of the government "social
insurance/security" as a PK. It's not _supposed_ to change, but it
can.

Supposing somebody does a "steal my identity" thing using my SIN/SSN
number, and things go so gravely badly that the government actually
decides that it is a better thing to give me a new number, that
_breaks_ the use of SSN/SIN as a primary key.

If we fabricate a number of our own as an "employee ID," that's well
and good, until such time as there is a corporate merger that has
conflicting ID spaces so that peoples' IDs have to change.
--
(format nil "~S@~S" "cbbrowne" "acm.org")

http://cbbrowne.com/info/sgml.html

"That's convenience, not cracker-proofing. Security is an emergent
property, not a feature." -- void <float@interpor t.net>

[Hans Forbrich]

Re: Separate foreign keys with shared ID space

Marshall Spight wrote:
[color=blue]
> "Christian Antognini" <christian.anto gnini@trivadis. com> wrote in message
> news:410affcf$1 @post.usenet.co m...[color=green]
>>
>> A PK should have no business meaning.[/color]
>
> Says who? Can you justify this statement?[/color]

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

[Greg D. Moore \(Strider\)]

Re: Separate foreign keys with shared ID space


"Marshall Spight" <mspight@dnai.c om> wrote in message
news:LPQOc.6392 2$eM2.25470@att bi_s51...[color=blue]
> "Christian Antognini" <christian.anto gnini@trivadis. com> wrote in message[/color]
news:410affcf$1 @post.usenet.co m...[color=blue][color=green]
> >
> > A PK should have no business meaning.[/color]
>
> Says who? Can you justify this statement?[/color]

Hmm, I want to see what Joe Celko says about this one....

[color=blue]
>
>
> Marshall
>
>[/color]

[Dan]

Re: Separate foreign keys with shared ID space


"Christophe r Browne" <cbbrowne@acm.o rg> wrote in message
news:2n27o8Fs39 uuU1@uni-berlin.de...[color=blue]
> After a long battle with technology, "Marshall Spight" <mspight@dnai.c om>,[/color]
an earthling, wrote:[color=blue][color=green]
> > "Christian Antognini" <christian.anto gnini@trivadis. com> wrote in[/color][/color]
message news:410affcf$1 @post.usenet.co m...[color=blue][color=green][color=darkred]
> >>
> >> A PK should have no business meaning.[/color]
> >
> > Says who? Can you justify this statement?[/color]
>
> A good reason for this is that business meanings can change, but
> primary keys can't.[/color]

On the contrary, primary keys, as with any key, can change as long as they
don't run afoul of another primary key value.
[color=blue]
>
> A typical example of this is the use of the government "social
> insurance/security" as a PK. It's not _supposed_ to change, but it
> can.
>[/color]
[color=blue]
> Supposing somebody does a "steal my identity" thing using my SIN/SSN
> number, and things go so gravely badly that the government actually
> decides that it is a better thing to give me a new number, that
> _breaks_ the use of SSN/SIN as a primary key.[/color]

Why?

CREATE TABLE old_ssns
(
old_ssn CHAR(9),
current_ssn CHAR(9),
PRIMARY KEY (old_ssn)
);

UPDATE people
SET SSN = <new SSN>,
WHERE SSN = <old SSN>

INSERT INTO old_ssns
(old_ssn, current_ssn)
VALUES (<old SSN>, <new SSN>);

COMMIT;

[color=blue]
>
> If we fabricate a number of our own as an "employee ID," that's well
> and good, until such time as there is a corporate merger that has
> conflicting ID spaces so that peoples' IDs have to change.[/color]

I agree that this is a problem but, it is a problem of logic and reflecting
how the world works and changes. I think that convoluting the notion of
keys (which are by definition a subset of properties of some entity)
reflecting semantics of certain properties to somthing different is not
always the proper approach.

Codd actually went into this in depth with his RM/2 proposal, which I really
need to reread. I think his proposal and ideas haven't been given the
attention they deserve.

- Dan[color=blue]
> --
> (format nil "~S@~S" "cbbrowne" "acm.org")
> http://cbbrowne.com/info/sgml.html
> "That's convenience, not cracker-proofing. Security is an emergent
> property, not a feature." -- void <float@interpor t.net>[/color]

[Erland Sommarskog]

Re: Separate foreign keys with shared ID space

Marshall Spight (mspight@dnai.c om) writes:[color=blue]
> "Christian Antognini" <christian.anto gnini@trivadis. com> wrote in message[/color]
news:410affcf$1 @post.usenet.co m...[color=blue][color=green]
>>
>> A PK should have no business meaning.[/color]
>
> Says who? Can you justify this statement?[/color]

Because most real-world look-like keys do not live up to the strict
requirements of the primary keys in a database. They are too often
nullable or non-unique. Person identification numbers such as SSN
are a good example of both.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Howard J. Rogers]

Re: Separate foreign keys with shared ID space


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns953819D CF3B6Yazorman@1 27.0.0.1...[color=blue]
> Marshall Spight (mspight@dnai.c om) writes:[color=green]
> > "Christian Antognini" <christian.anto gnini@trivadis. com> wrote in[/color][/color]
message[color=blue]
> news:410affcf$1 @post.usenet.co m...[color=green][color=darkred]
> >>
> >> A PK should have no business meaning.[/color]
> >
> > Says who? Can you justify this statement?[/color]
>
> Because most real-world look-like keys do not live up to the strict
> requirements of the primary keys in a database. They are too often
> nullable or non-unique. Person identification numbers such as SSN
> are a good example of both.[/color]

The problem I have with synthetic primary keys is precisely that they
abstract themselves so far away from business logic and hence become
'meaningless' that they cease to do the job they were intended to do. If I
insert a new record into the PERSONS table, using only a sequence number
generator to supply a new, unique ID for the row, I am pretty well
guaranteed to be able to insert the new record, even for a criminal clone of
Al Capone. If I use the SSN as a primary key field, however, it is highly
likely that my insert will (correctly) fail whenever someone is trying to do
a bit of identity theft. Sure, I could use a synthetic primary key, and then
add a unique constraint to a field such as SSN... but then I am merely doing
to the SSN manually what the declaration of it as a primary key would have
done in the first place. Meanwhile, there's a second index that I now have
to bear the costs of maintaining instead of just one.

For me, a primary key should absolutely embody a business rule ("no two
people can share a SSN", for example), since a primary key is a *logical*
way of uniquely referring to every row in the table, and logic and business
rules are close companions. If I want a non-nullable, meaningless and
guaranteed unique *physical* reference to my rows, I have the ROWID for
that. Synthetic keys seem to me to merely replicating the same sort of
functionality that the ROWID gives one in any case.

There are arguments both ways, of course. And some of the 'big names' are
definitely fans of synthetic keys (Steve Adams springs to mind), which
should give me pause. But I still don't like the extra unique checks needed
when synthetics are employed. And it seems to me that, in any case, there is
no way the original bald statement should be accepted without qualification.

Regards
HJR

[Erland Sommarskog]

Re: Separate foreign keys with shared ID space

Howard J. Rogers (hjr@dizwell.co m) writes:[color=blue]
> The problem I have with synthetic primary keys is precisely that they
> abstract themselves so far away from business logic and hence become
> 'meaningless' that they cease to do the job they were intended to do. If
> I insert a new record into the PERSONS table, using only a sequence
> number generator to supply a new, unique ID for the row, I am pretty
> well guaranteed to be able to insert the new record, even for a criminal
> clone of Al Capone. If I use the SSN as a primary key field, however, it
> is highly likely that my insert will (correctly) fail whenever someone
> is trying to do a bit of identity theft.[/color]

So you use SSN. Now you want to enter me in your database. What is my
SSN?
[color=blue]
> Sure, I could use a synthetic primary key, and then
> add a unique constraint to a field such as SSN...[/color]

At least then you have less problem when someone's SSN changes. And
you can handle persons without SSNs. (That is, your constraint should
be unique-when-NULL.)

You can also apply a partial validation rule. If the person is a Swedish
tax payer, I may require that is person identifcation number is unique
(because our customers reports to Swedosh tax authorities), but if he
pays tax in Upper Fragonia I could care less what his identification
numbers they may use there.
[color=blue]
> For me, a primary key should absolutely embody a business rule ("no two
> people can share a SSN", for example),[/color]

The problem is that the real-world business rule is different. Two
persons *can* have the same person identification number. It can be
because of systems that have goofed, or it can be because they live
in different countries - but still lives in the same system.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp