2

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

  • I would combine user and group table together and use a flag to indicate whether a row represent a user or group , I assume almost all of your columns can be made general if you remove it from the context of your table structure , i.e userid,groupid -----> ID – entDba Feb 18 '17 at 2:49
  • Looks like a pretty solid design to me. – wogsland Feb 18 '17 at 3:18
  • @entDba Groups and users aren't the same (e.g. a user can login and a group cannot, a group contains users but a user does not), so I think they're not exactly the same table; a machine-readable ID and a displayable name are all they have in common. I could have a parent table of IDs, which users, groups, and resources all use as a parent table; do you think that would be good? But that schema wouldn't or couldn't ensure that each ID in IDs table the has a corresponding group or user record. – ChrisW Feb 18 '17 at 9:54
1

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
|improve this answer|||||
  • 1
    Thank you for suggesting this alternative. The only downside that I see is that it's not easy to contrain that every row in resources has an owner, i.e. that there exists a corresponding row in userResources or groupResources (because it's necessary to insert into resources before inserting into userResources or groupResources, and I think that MS-SQL doesn't support "deferred" contraints which aren't checked until the end of the transaction). – ChrisW Feb 18 '17 at 14:23
  • @ChrisW That is a good point. – SqlZim Feb 18 '17 at 14:47
1

What you have is not bad

  • Create a group for each user
  • Create a super table Owner with User and Group each pointing to it. The problem is cannot prevent both User and Group pointing to the same resource. Cannot be sure no user or group points to a resource.
  • Waste columns and have both user and group in the same table
|improve this answer|||||
  • "Create a group for each user" is neat, especially if I do something to ensure that every group contais a least one user. – ChrisW Feb 18 '17 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.