By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 1,885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

multiple foreign keys on same field, based on other field

P: n/a
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.

Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.

Jul 23 '05 #1
Share this Question
Share on Google+
26 Replies


P: n/a
On 2 Mar 2005 15:29:16 -0800, pb648174 wrote:
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.

Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.


Hi pb648174,

If I understand you correctly, each row in BidFolder is related to
either one row in BidItem or to one row in BidAddendum. Correct so far?

Am I also correct that both BidItem and BidAddendum have RefId as either
PRIMARY KEY or UNIQUE column, so that this column can be used in a
FOREIGN KEY constraint?

The way I would implement this, is to have two RefId columns in the
BidFolder table (of course appropriately named), with a CHECK constraint
to ensure that exactly one of them is populated and the other is NULL:

CREATE TABLE BidFolder
( .......
, .......
, Item_RefId ? DEFAULT NULL -- Replace ? with
, Addendum_RefId ? DEFAULT NULL -- the correct type
, .....
, PRIMARY KEY (....)
, FOREIGN KEY (Item_RefId) REFERENCES BidItem
, FOREIGN KEY (Addendum_RefId) REFERENCES BidAddendum
, CHECK ((Item_RefId IS NULL AND Addendum_RefID IS NOT NULL)
OR (Item_RefId IS NOT NULL AND Addendum_RefID IS NULL))
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2

P: n/a
I am not totally sure i understand what you are trying to do, but
could you do someting like

BidItem
PK RefId
PK Type - Set default to 2
FK BidAddendum_RefId
FK BidAddendum_Type

BidAddendum
PK RefId
PK Type - Set default to 1
FK BidItem_RefId
FK BidItem_Type
BidFolder
PK Whatever
FK RefId
FK Type


"pb648174" <go****@webpaul.net> wrote in message news:<11**********************@l41g2000cwc.googleg roups.com>...
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.

Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.

Jul 23 '05 #3

P: n/a
No, BidItem and Addendum do not have the type and refid fields. The
Type and RefId columns are only in the BidFolder table and are used to
associate one or more BidFolders with either a BidItem (Type 1) or
BidAddendum (Type 2). I don't htink I can do a compound foreign key
based on the type of 1 or 2, so I'm wondering how I set a foreign key
in this scenario.

Another person suggested using two different columns in the BidFolder
table, which would work but would not be very flexible moving forward,
since if I had another relationship to map, I would have to update all
the tables and stored procs instead of just adding BidFolder entries
with a type of 3.

Jul 23 '05 #4

P: n/a
P B


That works, but if I want to add a third relationship I have to update
the table and all associated stored procedures.. Is there any way to
accomplish it with the existing Type and RefId columns?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 23 '05 #5

P: n/a
On 3 Mar 2005 09:59:52 -0600, P B wrote:
That works, but if I want to add a third relationship I have to update
the table and all associated stored procedures.
Hi P B,

Adding a column to a table is not that much work. One ALTER TABLE
statement for the column and one ALTER TABLE statement for the
constraint is all you need. You might also need to run an UPDATE to fill
the new columns with the correct starting data, but you'd need to do
that anyway, regardless of the chosen representation.

Stored procedures that have to do something functional with the third
relationship need to be updated anyway. Stored procedures that don't
need to handle the third relationship don't need to be updated (unless
you use INSERT without column list or SELECT * - but both are bad
practice in a production system anyway).

Is there any way to
accomplish it with the existing Type and RefId columns?


Yes, it's pointed out by Linn. Here's a link to a more verbose
explanation of the same principle by Joe Celko:
http://groups-beta.google.com/group/...b18c87e1743165
(beware of possible line wrapping)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #6

P: n/a
It is a huge amount of work when there is thousands of line of existing
code - I don't want to go and change all the function and stored
procedures referencing these tables - I just want to get foreign key
relationships on the existing tables, so as long as the data structure
changes don't affect current stored procedures, the application code
won't need to be changed either.

The solution you posted a link to is a neat idea, but it is modeling an
"is-a" relationship whereas this is a "has-a" relationship. Let's say I
did the following, to follow the suggestions given so far: (the types
don't exist right now in the Item and Addendum tables, but could be
added easily since they have a default value and will not be referenced
in existing stored procedures)

BidItem
Type 1(default value)
Id
BidAddendum
Type 2(default value)
Id

BidFolder
Type 1 or 2
RefId (references Id in Item or Addendum tables based on Type)

A particular BidItem or BidAddendum will have multiple BidFolder
entries. If I could, I would like to put multiple foreign keys on the
BidFolder table to reference the BidItem and BidAddendum tables, but I
can't do that - I also don't think placing the foreign keys on the
BidItem/Addendum tables will work since the relationship is one to many
from that perspective instead of many to one.

Jul 23 '05 #7

P: n/a
P B (de***********@webpaul.net) writes:
That works, but if I want to add a third relationship I have to update
the table and all associated stored procedures.. Is there any way to
accomplish it with the existing Type and RefId columns?


If you arrive to this situation - or if you think you can arrive at
this situation - then maybe you need to take a broader look at your
database design.

One alternative is to create a supertable to the parents, and then have
the FK to refer to that table. That table would look like:

CREATE TABLE mothertable (refid ...,
type ....,
PRIMARY KEY(refid),
UNIQUE (refid, type))

The seemingly superfluous UNIQUE constraint, permits you to use an
FK from you lower table.

You could also give up on DRI, and use a trigger instead.

When I have encountered this, I have gone for Hugo's solution in
most cases.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

P: n/a
pb648174 (go****@webpaul.net) writes:
It is a huge amount of work when there is thousands of line of existing
code - I don't want to go and change all the function and stored
procedures referencing these tables - I just want to get foreign key
relationships on the existing tables, so as long as the data structure
changes don't affect current stored procedures, the application code
won't need to be changed either.


Thousands of line of code? That's not much. :-)

If you don't want to change the code, but use the tables as they are,
you will have to go for a trigger.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9

P: n/a
I was hoping to use foreign keys so that replication would be smart
enough to pick up all the relationships.. Will replication correctly
handle triggers automatically or will I need to setup something special
for this scenario?

How would you even accomplish Hugo's suggestion for a "has-a"
relationship? His involves setting the foreign key on the
BidItem/Adendum tables which would be a one to many instead of a many
to one relationship.

Jul 23 '05 #10

P: n/a
On 3 Mar 2005 14:44:41 -0800, pb648174 wrote:
It is a huge amount of work when there is thousands of line of existing
code - I don't want to go and change all the function and stored
procedures referencing these tables
Hi pb,

Wrong arguments. :-)

Always strive for a good design. A design that saves you work now but is
not the best design for the situation will almost certainly cost you (or
your company) dearly in the long run.

"Why is there never the time/money to do it good, but always the
time/money to do it over?"

The solution you posted a link to is a neat idea, but it is modeling an
"is-a" relationship whereas this is a "has-a" relationship.


First, I'm not sure if having one BidFolder table is the correct design
in your case. Does the real world that you attempt to model really have
one entity BidFolders that belong either to a BidItem or to a
BidAddendum, but are of the same type otherwise? Or are you using one
table to store information about two different things, just because
their structure is so similar that it looks more convenient?

If the correct design really involves one BidFolder table, then I'd
definitely favor the solution in my first post, with seperate columns
for the relationship to BidItem and the relationship to BidAddendum,
since they are different things that should be stored in different
columns. If you store information about persons, and you store either
weight (in kg) or age (in years), but never both, would you combine both
in one integer column, with a seperate Type column to indicate whether
the 68 stored in one column is a weight or an age? I really hope you
wouldn't - so why would you treat foreign key columns differently?
This being said, Celko's subtype-solution I posted a link to can be
adapted for 1 to many relationships. See the example below. But keep in
mind that if you worked for me and came up with this solution, you
better had some very good arguments why it's the best solution in this
specific case, or you'd run a high risk of losing your bonus and maybe
even your job.

CREATE TABLE BidItem
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 1
, ....
, PRIMARY KEY (RefId)
, UNIQUE (RefId, Type)
, CHECK (Type = 1)
)
CREATE TABLE BidAddendum
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 2
, ....
, PRIMARY KEY (RefId)
, UNIQUE (RefId, Type)
, CHECK (Type = 2)
)
CREATE TABLE BidFolder
( .......
, .......
, RefId int NOT NULL
, Type tinyint NOT NULL
, .....
, PRIMARY KEY (....)
, FOREIGN KEY (RefId, Type) REFERENCES BidItem (RefId, Type)
, FOREIGN KEY (RefId, Type) REFERENCES BidAddendum (RefId,
Type)
, CHECK (Type IN (1, 2))
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #11

P: n/a
>From this vague narrative, it sounds like you are mimicking paper forms
with tables. The name suggest paper forms, but the data sounds like
it should be a history of a bid or that there are many logically
different kinds of bids that need their own tables.
I would prefer not to have multiple identical tables. <<


That would be a major design flaw; tables that are
identical model the same kinds of things in two places.

Jul 23 '05 #12

P: n/a
Well all I'll say is that if solution 1 offer maintenance costs lower
than solution 2 with identical performance, then it's a pretty easy
sell for me to use the solution with lower maintenance costs. Users and
management couldn't care what the underlying structure is as long as it
works and performs well.

Regarding the above solution, is there an OR relationship implicit with
multiple foreign keys? I tried looking up that information in the SQL
books but didn't find anything on it. How would you model an AND
relationship with multiple foreign keys if that is the case? (Just
curious)

Jul 23 '05 #13

P: n/a
pb648174 (go****@webpaul.net) writes:
Well all I'll say is that if solution 1 offer maintenance costs lower
than solution 2 with identical performance, then it's a pretty easy
sell for me to use the solution with lower maintenance costs. Users and
management couldn't care what the underlying structure is as long as it
works and performs well.

Regarding the above solution, is there an OR relationship implicit with
multiple foreign keys? I tried looking up that information in the SQL
books but didn't find anything on it. How would you model an AND
relationship with multiple foreign keys if that is the case? (Just
curious)


I think Hugo's made a mistake his table. I would be very difficult
to insert anything at all in BidFolder with that design! This is probably
what he had in mind:
CREATE BidParents (Refid int NOT NULL,
Type tinyint,
PRIMARY KEY (RefId),
UNIQUE (RefId, Type)

CREATE TABLE BidItem
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 1 CHECK (Type = 1)
, ....
, PRIMARY KEY (RefId)
, FOREIGN KEY (RefId, Type) REFERENCES BidParents(RefId, Type)
, CHECK (Type = 1)
)
CREATE TABLE BidAddendum
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 2 CHECK (Type = 2)
, ....
, PRIMARY KEY (RefId)
, FOREIGN KEY (RefId, Type) REFERENCES BidParents(RefId, Type)
,
)
CREATE TABLE BidFolder
( .......
, .......
, RefId int NOT NULL
, .....
, PRIMARY KEY (....)
, FOREIGN KEY (RefId, Type) REFERENCES BidParents (RefId)
)

Since you know your business domain better than we know, you can
say whether this makes any sense at all.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #14

P: n/a
pb648174 (go****@webpaul.net) writes:
I was hoping to use foreign keys so that replication would be smart
enough to pick up all the relationships.. Will replication correctly
handle triggers automatically or will I need to setup something special
for this scenario?
It was very long ago since I looked at replication, and that was in
6.5 days. I will have to admit that I have little knowledge of the
implication of both triggers and foreign keys for replication.
How would you even accomplish Hugo's suggestion for a "has-a"
relationship? His involves setting the foreign key on the
BidItem/Adendum tables which would be a one to many instead of a many
to one relationship.


I'm not sure exactly which suggestion you are thinking of. But see
my other post for tonight for possible scheme for your case. (I still
prefer to have two different columns, though.)

Anyway, "has-a" is object-orienting thinking, but you are in a relational
database now. It's not really the same thing as Kansas.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #15

P: n/a
On 4 Mar 2005 07:04:21 -0800, pb648174 wrote:
Regarding the above solution, is there an OR relationship implicit with
multiple foreign keys? I tried looking up that information in the SQL
books but didn't find anything on it. How would you model an AND
relationship with multiple foreign keys if that is the case? (Just
curious)


Hi PB,

As Erland pointed out, I made an error. That'll teach me to post
solutions without testing them first. <hangs head in shame>

The solution I posted, with two foreign keys, will attempt to enforce
them both. And since the CHECK constraints in the two references tables
are mutually exclusive, you'll never be able to enter any data at all.

I don't see other solutions than the one suggested by Erland. Of course,
that changes the model from the two "has-a" relationships you asked
about to one "has-a" relationship to a supertype, that has two "is-a"
relationships to it's subtypes. Not quite the same :-)

All the more reason to go for my original solution with one column for
each foreign key relationship, methinks. (Unless, of course, the
BidItems and the BidAddendums are indeed conceptually subtypes of one
common supertype).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #16

P: n/a
Yeah, this is a big mess. This is one of my more simpler relationships
being modeled in SQL - I was doing it as a test to see if I could map
foreign keys for all the relationships. I think I'm just going to leave
it alone and let it be done in application logic.

Jul 23 '05 #17

P: n/a
On 4 Mar 2005 22:42:58 -0800, pb648174 wrote:
Yeah, this is a big mess. This is one of my more simpler relationships
being modeled in SQL - I was doing it as a test to see if I could map
foreign keys for all the relationships.
Hi PB,

It doesn't need to be a mess. It can actually be quite simple. It only
becomes complicated if you try to use a single column for more than one
relationship.

If you just add seperate columns for each relationship and define a
FOREIGN KEY constraint on each of those columns, you'll have a simple
design that's easy to use and easy to understand.

I think I'm just going to leave
it alone and let it be done in application logic.


That is never a good idea. Applications can fail. Always use declarative
referential integrity (DRI) in the database to make sure that your data
can't get corrupted by application errors, or by wizz-kids bypassing the
application.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #18

P: n/a
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
It doesn't need to be a mess. It can actually be quite simple. It only
becomes complicated if you try to use a single column for more than one
relationship.


And most of all, it will be a mess if you try squeeze squared SQL Server
pegs through object-oriented circular holes. I have a feeling that this
is what is the crux of the biscuit in this case.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #19

P: n/a
Is it all that unreasonable to want to add a different type of item
without having to rewrite all the stored procedures and modify all the
application logic? Adding a column is a big deal, not because of the
SQL statement to add the column, but because of all the consequences.
This is a fairly simple example, I have some much more complicated
ones, with a table referencing itself and one with 8 or 9 types
(instead of 2).

If taking care of the relationship in the application is a bad idea,
it's the best bad idea I see so far.

Jul 23 '05 #20

P: n/a
pb648174 (go****@webpaul.net) writes:
Is it all that unreasonable to want to add a different type of item
without having to rewrite all the stored procedures and modify all the
application logic? Adding a column is a big deal, not because of the
SQL statement to add the column, but because of all the consequences.
This is a fairly simple example, I have some much more complicated
ones, with a table referencing itself and one with 8 or 9 types
(instead of 2).


You will have to understand that neither I nor Hugo has very poor knowledge
about your business domain. All we know are the names of the tables, and
it does not tell me that much. What we can to is to build from our own
experiences. In the database I work with, there are quite a few cases of
mutually exclusive columns. The number is almost always two, although I
recall that somewhere the number is three. Given what you told us, this
appeared to be the correct solution.

Now you say that you have 8-9 different tables you can refer to. In this
case, I doubt that it is a good idea to have 8-9 different columns with
a constraint specifying that exactly one must be non-NULL. I'm tempted
to say that there might be reason to review the entire data model. And
surely some variation of the suprertype concept is more apt here.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #21

P: n/a
On 5 Mar 2005 16:57:39 -0800, pb648174 wrote:
Adding a column is a big deal, not because of the
SQL statement to add the column, but because of all the consequences.


Hi PB,

Could you elaborate on this? I've been trying to think what consequences
you refer to, but I don't see them. I might be missing the obvious, of
course - but there's also the possibility that there's a very simple
solution to what you perceive as a problem. If you post it, others can
try to help you!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #22

P: n/a
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
Could you elaborate on this? I've been trying to think what consequences
you refer to, but I don't see them. I might be missing the obvious, of
course - but there's also the possibility that there's a very simple
solution to what you perceive as a problem. If you post it, others can
try to help you!


Of course, you don't add a column to a table only to have it sit
there, but there will be some code that will have to be added to
retrieve, display, and update the new column.

If you use SELECT *. INSERT without no column lists, and other bad
programming constructs, the impact can be even harder.

So, if it really is the case that tomorrow a third table can be
targeted, then this solution is not a good one. But I also think
that there is something with the data model that needs fixing in
this case.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #23

P: n/a
Ok, forget about the example I posted and consider how you would model
this then: You have an audit log which logs the exact same information
for every single module in an application. In addition to viewing the
log, one requirement is that users be able to click on "details" for
each audit log line item and go directly to the module the audit log
involves.

So obviously there needs to be a relationship between the audit log
line item and all the modules in the system(20-30). Furthermore, new
modules are introduced every quarter, so it should be relatively easy
to add new modules to the auditing system without having to touch the
existing code (so it doesn't have to be re-tested).

How would you model it? And better yet, how would you do the foreign
keys?

Jul 23 '05 #24

P: n/a
pb648174 (go****@webpaul.net) writes:
Ok, forget about the example I posted and consider how you would model
this then: You have an audit log which logs the exact same information
for every single module in an application. In addition to viewing the
log, one requirement is that users be able to click on "details" for
each audit log line item and go directly to the module the audit log
involves.

So obviously there needs to be a relationship between the audit log
line item and all the modules in the system(20-30). Furthermore, new
modules are introduced every quarter, so it should be relatively easy
to add new modules to the auditing system without having to touch the
existing code (so it doesn't have to be re-tested).

How would you model it? And better yet, how would you do the foreign
keys?


The hour is late, and the descrption is brief, so I'll be brief too.

1) For an audit log, I could consider scrapping referential integrity. Or
have some table that is specific to the audit look to server as lookup
for id:s of the modules.

2) If DRI to the audited rows is absolutely desired, I would consider of
adding the table "auditable_items" that would hold all items that are
common to audited tables. All auditied tables would refer to that
table by the trick that I demonstrated earlier. (And of which credits
for the idea goes to Joe Celko.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #25

P: n/a
Well it's refreshing to hear the same idea suggested that I used when
creating it... Especially after all this talk of bad design.

I am a bit unclear on what you mean by this though:
"Or
have some table that is specific to the audit look to server as
lookup
for id:s of the modules."

Jul 23 '05 #26

P: n/a
pb648174 (go****@webpaul.net) writes:
Well it's refreshing to hear the same idea suggested that I used when
creating it... Especially after all this talk of bad design.

I am a bit unclear on what you mean by this though:
"Or have some table that is specific to the audit look to server as
lookup for id:s of the modules."


One hardly blame you for not getting sense in this gibberish. I did say
that it was late at night, didn't I? :-)

I think that what I meant was that rather having an audit table that
looks like:

CREATE TABLE auditlog (modulename varchar(40) NOT NULL,
....

You would have

CREATE TABLE auditlog (moduleid int NOT NULL,
....

And then moduleid would be an FK to the modules table, which probably
should be specific for the audting. The main advantage is that you gain
some size with the log table.

Actually, in our system we have one general log table where you can
log about everything in datachanges. This table is very simple. We
have (tablename, keyvalue1, keyvalue2) to identify the changed row,
and "colname" to identify the column. There is no referential integrity
at all from the log table.

But you may have more heavy-duty requirements than we have.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #27

This discussion thread is closed

Replies have been disabled for this discussion.