473,746 Members | 2,589 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Separate foreign keys with shared ID space

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
Jul 20 '05 #1
31 3377
**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert
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?


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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Jul 20 '05 #2
ro************* @yahoo.com (Robert Brown) wrote in message news:<24******* *************** ****@posting.go ogle.com>...
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


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
Jul 20 '05 #3
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" <ch************ *****@trivadis. com> wrote in message news:<41******* *@post.usenet.c om>...
**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert
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?


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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Jul 20 '05 #4
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.
Jul 20 '05 #5
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.

le*****@kommuni cera.umea.se (Lennart Jonsson) wrote in message news:<6d******* *************** ****@posting.go ogle.com>...
ro************* @yahoo.com (Robert Brown) wrote in message news:<24******* *************** ****@posting.go ogle.com>...
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


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

Jul 20 '05 #6
**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert
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?


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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Jul 20 '05 #7
"Christian Antognini" <ch************ *****@trivadis. com> wrote in message news:41******** @post.usenet.co m...

A PK should have no business meaning.


Says who? Can you justify this statement?
Marshall
Jul 20 '05 #8
After a long battle with technology, "Marshall Spight" <ms*****@dnai.c om>, an earthling, wrote:
"Christian Antognini" <ch************ *****@trivadis. com> wrote in message news:41******** @post.usenet.co m...

A PK should have no business meaning.


Says who? Can you justify this statement?


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 <fl***@interpor t.net>
Jul 20 '05 #9
Marshall Spight wrote:
"Christian Antognini" <ch************ *****@trivadis. com> wrote in message
news:41******** @post.usenet.co m...

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
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
42420
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed to be optional, it's very definition is it's a necessary link to the parent table and part of the definition. If it's optional it shouldn't be part of the definition of a table and should be in a linking table instead. Comments?
0
1418
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A stored procedure updates a row in C, adds a row each in B & C. I get an integrity violation. All the foreign keys are deferrable, and the stored procedure is called from within a transaction with constraints deferred. (And the foreign keys do refer to...
10
17852
by: D. Dante Lorenso | last post by:
I'd like to run a clean up command on my tables to eliminate rows that I'm no longer using in the database. I want to do something like this: DELETE FROM tablename WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE; Does anyone know how something like this could be done in PostgreSQL? I know I can search all the tables that
9
2399
by: Jax | last post by:
I'm making my first ever database for my program. I understand the concept of one to many relationships but fail to see the advantage of using a secondary key over a primary one. I have a lot of tables and link them together by one key the CaseKey. What is the disadvantages of this method? Why should I include more detailed item keys (e.g an arraylist of customer credit items would be stored in the CustomerCreditItem table surely it's...
3
5384
by: Alex Satrapa | last post by:
There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from inheriting tables, eg: CREATE TABLE foo ( id SERIAL PRIMARY KEY ); CREATE TABLE bar ( attribute integer NOT NULL ) INHERITS (foo);
1
2092
by: Thomas T. Thai | last post by:
I'm looking for a better way to make use of foreign keys. Here is a sample setup: -- TESTING Foreign Keys create table mod ( mod_id int not null primary key, name varchar(32) not null default '' );
10
2095
by: Shawn Chisholm | last post by:
Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a particular table? Deferring the locks is unfortunately not a good option for me... Thanks, Shawn ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
8
11804
by: shsandeep | last post by:
What is the quickest way to drop all the foreign keys in a database? Only FKs, not PKs. Cheers, San.
116
5203
by: Neil | last post by:
Whenever I've created junction tables in the past, I always made the PK of the junction table the combined pks from the two other tables. Ex: Table1 PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which together comprise the PK for the junction table. However, I just came across some code in which the person created a junction table with a separate PK consisting of an autonumber field, and then the two fields. So I...
0
8974
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9350
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8229
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6772
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6060
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4586
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4836
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3292
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2765
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.