Multipurpose Foreign Key

[Jamie Jackson]

I've got a field [user.unitID] that can be a foreign key on the table
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).

Question 1: Is this poor DB design?
Question2: If this is okay DB design (or, if I can't change this DB),
how do I perform a join?

I started writing a sproc (see "Beginnings of sproc," below), which
will definitely work, once I get it set up, but when I do these sorts
of things, I later come to find that there was a straight SQL way to
do it. So, is there a straight SQL way to do this, building joins with
CASEs, or something like that?

Thanks,
Jamie

## Structure (simplified) ##

[USER]
userID
unitID
role -- values can be 'B' or 'C' referring to [base] or [command] tbl

[BASE]
ID
NAME

[COMMAND]
ID
NAME

## Beginnings of a sproc ##

GO
USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'getSessionInfo' AND type = 'P')
DROP PROCEDURE getSessionInfo
GO
USE myDB
GO
CREATE PROCEDURE getSessionInfo
@userID varchar(50),
@password varchar(50)
AS

DECLARE @myUnitType varchar(2);

SELECT @myUnitType = unitType
FROM
[user]
WHERE userID = @userID
AND [password] = @password

.... blah blah blah

[Erland Sommarskog]

Jamie Jackson (wa***************@bigfoot.com) writes:

I've got a field [user.unitID] that can be a foreign key on the table
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).

Question 1: Is this poor DB design?
It is certainly not the plain standard design. And the method has the
apparent advantage that you can use FOREIGN-KEY constraint to enforce
the integrity, but you need to rely on triggers.

But since I know very little of your business problem, I am hesitant
to label the design as outright bad, or even poor. What I can say, is
that had I had the problem, I would definitely have looked into a solution
that would have permitted me to use DRI, but that would definitely have
been a case of fitting the solution to the tool.
Question2: If this is okay DB design (or, if I can't change this DB),
how do I perform a join?

Depends a little on the output, but say you want user and name of
base or command:
SELECT u.name, u.role, rolename = coalece(b.name, c.name)
FROM users u
LEFT JOIN base b ON u.role = 'B'
AND u.unitid = b.unitid
LEFT JOIN command c ON u.role = 'C'
AND u.unitid = c.unitid

An alternative is to introduce a basecommand table, to gather common
information, but I don't know enough about your business problem to
say whether this is a good idea or not.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

Jamie Jackson wrote:

I've got a field [user.unitID] that can be a foreign key on the table
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).

Question 1: Is this poor DB design?

<snip>

Yes. A table represents something. In your case a User, a Base or a
Command. In your design you specify whether a User can have a relation
with a Base or not. If it can have a relation, then this means there is
a foreign key column (or a relation table) for this specific relation.
For Command you make the same decision.

So in your case, User should have one column with a foreign key
constraint to Base, and another column with a foreign key constraint to
Command. You make the relations optional by allowing NULL values in the
foreign key columns.

If this is an Object Oriented design (for example, when Command extends
from Base), then you are in trouble, because OO and RDBMS don't map very
well.

Hope this helps,
Gert-Jan

[Jamie Jackson]

Thanks, Erland, that SQL was a nice tutorial on filtered joins and the
coalesce function, neither of which I've ever used.

Thanks,
Jamie

On Wed, 16 Jul 2003 21:54:51 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:

Jamie Jackson (wa***************@bigfoot.com) writes:

I've got a field [user.unitID] that can be a foreign key on the table
[Base] *OR* the table [Command] depending on the value of [User.role]
(see "Structure," below).

Question 1: Is this poor DB design?

It is certainly not the plain standard design. And the method has the
apparent advantage that you can use FOREIGN-KEY constraint to enforce
the integrity, but you need to rely on triggers.

But since I know very little of your business problem, I am hesitant
to label the design as outright bad, or even poor. What I can say, is
that had I had the problem, I would definitely have looked into a solution
that would have permitted me to use DRI, but that would definitely have
been a case of fitting the solution to the tool.

Question2: If this is okay DB design (or, if I can't change this DB),
how do I perform a join?

Depends a little on the output, but say you want user and name of
base or command:
SELECT u.name, u.role, rolename = coalece(b.name, c.name)
FROM users u
LEFT JOIN base b ON u.role = 'B'
AND u.unitid = b.unitid
LEFT JOIN command c ON u.role = 'C'
AND u.unitid = c.unitid

An alternative is to introduce a basecommand table, to gather common
information, but I don't know enough about your business problem to
say whether this is a good idea or not.