FK "this or that" case best practice

If I have a table that can either have a FK to one table or another, what is
the best way to design it?
eg.
create table template
(
id int not null primary key,
description varchar(50) not null,
roleId int null references role(roleId),
userId int null references user(userId)
)
At the moment, because either a role or a user can own the template, I
haveset both columns to allow null, but I do not like this approach. Would
intermediate tables be better even though it is a 1:1 relationship?
create table template
(
id int not null primary key,
description varchar(50) not null,
)
create table templateuser
(
templateId int null references template(id) primary key,
userId int null references user(userId)
)
create table templaterole
(
templateId int null references template(id) primary key,
roleId int null references role(roleId)
)
Thanks
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--David,
One solution could be enforcing the RI using triggers.
AMB
"David J Rose" wrote:

> If I have a table that can either have a FK to one table or another, what
is
> the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||I think it is easier to go with one table, because then you can enforce with
a check constraint that a template is related to exactly a user or a role
(and not both or none), something which you can't do as easy if you use the
2 extra tables. The check constraint would be:
CONSTRAINT CK_template__either_role_or_user
CHECK((roleId IS NULL AND userID IS NOT NULL) OR (roleId IS NOT NULL AND
userID IS NULL))
Jacco Schalkwijk
SQL Server MVP
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||What is wrong with having two tables, one for users, one for roles. You
don't HAVE to have a row in both user and role, so this design makes sense.
I would put a FK to both tables, and probably set them to DELETE CASCADE.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--