multiple foreign keys on same field, based on other field

[pb648174]

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.

[Hugo Kornelis]

Re: multiple foreign keys on same field, based on other field

On 2 Mar 2005 15:29:16 -0800, pb648174 wrote:
[color=blue]
>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.[/color]

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)

[Linn K B]

Re: multiple foreign keys on same field, based on other field

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_Ref Id
FK BidAddendum_Typ e

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


BidFolder
PK Whatever
FK RefId
FK Type








"pb648174" <google@webpaul .net> wrote in message news:<110980615 6.005319.202280 @l41g2000cwc.go oglegroups.com> ...[color=blue]
> 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.[/color]

[pb648174]

Re: multiple foreign keys on same field, based on other field

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.

[P B]

Re: multiple foreign keys on same field, based on other field



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!

[Hugo Kornelis]

Re: multiple foreign keys on same field, based on other field

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

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).

[color=blue]
> Is there any way to
>accomplish it with the existing Type and RefId columns?[/color]

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/microsoft.public.sqlserver.programming/msg/adb18c87e1743165

(beware of possible line wrapping)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[pb648174]

Re: multiple foreign keys on same field, based on other field

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.

[Erland Sommarskog]

Re: multiple foreign keys on same field, based on other field

P B (developersdex@ webpaul.net) writes:[color=blue]
> 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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

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

[Erland Sommarskog]

Re: multiple foreign keys on same field, based on other field

pb648174 (google@webpaul .net) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

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

[pb648174]

Re: multiple foreign keys on same field, based on other field

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.

[Hugo Kornelis]

Re: multiple foreign keys on same field, based on other field

On 3 Mar 2005 14:44:41 -0800, pb648174 wrote:
[color=blue]
>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[/color]

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?"

[color=blue]
>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.[/color]

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)

[--CELKO--]

Re: multiple foreign keys on same field, based on other field

>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.
[color=blue][color=green]
>> I would prefer not to have multiple identical tables. <<[/color][/color]

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

[pb648174]

Re: multiple foreign keys on same field, based on other field

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)

[Erland Sommarskog]

Re: multiple foreign keys on same field, based on other field

pb648174 (google@webpaul .net) writes:[color=blue]
> 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)[/color]

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(RefI d, 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(RefI d, 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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

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

[Erland Sommarskog]

Re: multiple foreign keys on same field, based on other field

pb648174 (google@webpaul .net) writes:[color=blue]
> 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?[/color]

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.
[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

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