Sql-server – Either of two parent tables

database-designsql server

I have a table of users:

CREATE TABLE users
(
  userId INT PRIMARY KEY,
  userName VARCHAR(100),
  /* plus other user columns */
);

And a table of groups:

CREATE TABLE groups
(
  groupId INT PRIMARY KEY,
  groupName VARCHAR(100),
  /* plus other group columns */
);

And I have a table of resources; and I want each resource to be owned (i.e. to have a parent), either owned by a user or owned by a group.

Is the following the right way to model that:

CREATE TABLE resources
(
  resourceId INT PRIMARY KEY,
  userId INT NULL,
  groupId INT NULL,    
  /* plus other resource columns */

  FOREIGN KEY(userId) REFERENCES users(userId),
  FOREIGN KEY(groupId) REFERENCES groups(groupId),

  CHECK ((userId IS NULL) ^ (groupId IS NULL))
);

In other words I model it as two nullable columns, each with foreign key constraint, and a check constraint to ensure that exactly one of them is null.

Is this a good way to model that? It looks plausible to me but I don't remember reading it in a book, so I thought I should ask.

Is there a different way to model it? I guess I don't want to have two resources tables (e.g. userResources and groupResources), because they're the same kind of resource and because resources is itself a parent table of further tables (so I don't want to multiply the number of resources tables).

Best Answer

because resources is itself a parent table of further tables

As an alternative, you could model the ownership of resources the same way you would model the membership of users and groups:

I think this model might allow for more flexibility depending on other possible business requirements for ownership of resources.

create table users(
    userId   int not null primary key
  , userName varchar(100)
  /* plus other user columns */
);
create table groups(
    groupId   int not null primary key
  , groupName varchar(100)
  /* plus other group columns */
);

create table groupUsers(
    groupUsersId int identity(1,1) not null primary key 
  , groupId int not null
  , userId  int not null
  , constraint fk_groupUsers_Users 
      foreign key(userId) references users(userId)
  , constraint fk_groupUsers_Groups  
      foreign key(groupId) references groups(groupId)
);

and resources like:

create table resources(
    resourceId int not null primary key
  /* plus other resource columns */
);

create table userResources (
    userResourcesId int identity(1,1) not null primary key 
  , userId     int not null
  , resourceId int not null
  , constraint fk_userResources_Users
      foreign key(userId) references users(userId)
  , constraint fk_userResources_Resources 
      foreign key(resourceId) references resources(resourceId)
  /* if resource can only link to 1 user */
  , constraint uq_userResources_resourceId
      unique (resourceId)
);

create table groupResources (
    groupResourcesId int identity(1,1) not null primary key 
  , groupId    int not null
  , resourceId int not null
  , constraint fk_groupResources_Groups
      foreign key(groupId) references groups(groupId)
  , constraint fk_groupResources_Resources 
      foreign key(resourceId) references resources(resourceId)
  /* if resource can only belong to 1 group */
  , constraint uq_groupResources_resourceId
      unique (resourceId)
);

If you want to use check constraints to enforce that a resourceId is only in one of userResources and groupResources you could use something like this:

go
create function udf_resourceId_in_groupResources (@resourceid int)
returns bit as
    begin;
    if exists (select 1 from groupResources g where g.resourceId = @resourceId)
        return 1;
    return 0;
    end;
go
alter table userResources 
  add constraint chk_ResourceId_notin_groupResources
    check (dbo.udf_resourceId_in_groupResources(resourceId) = 0)
go
create function udf_resourceId_in_userResources (@resourceid int)
returns bit as
    begin;
    if exists (select 1 from userResources u where u.resourceId = @resourceId)
        return 1;
    return 0;
    end;
go
alter table groupResources 
  add constraint chk_ResourceId_noin_userResources
    check (dbo.udf_resourceId_in_userResources(resourceId) = 0);
go
Related Question