Foreign Key to multiple tables

Question

I ve got 3 relevant tables in my database   CREATE TABLE dbo Group       ID int NOT NULL      Name varchar 50  NOT NULL      CREATE TABLE dbo User       ID int NOT NULL      Name varchar 50  NOT NULL    CREATE TABLE dbo Ticket       ID int NOT NULL      Owner int NOT NULL      Subject varchar 50  NULL     Users belong to multiple groups   This is done via a many to many relationship  but irrelevant in this case   A ticket can be owned by either a group or a user  via the dbo Ticket Owner field     What would be the MOST CORRECT way describe this relationship between a ticket and optionally a user or a group   I m thinking that I should add a flag in the ticket table that says what type owns it

User · Answer

Another approach is to create an association table that contains columns for each potential resource type. In your example, each of the two existing owner types has their own table (which means you have something to reference). If this will always be the case you can have something like this:

CREATE TABLE dbo.Group
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)  

CREATE TABLE dbo.User
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner_ID int NOT NULL,
    Subject varchar(50) NULL
)

CREATE TABLE dbo.Owner
(
    ID int NOT NULL,
    User_ID int NULL,
    Group_ID int NULL,
    {{AdditionalEntity_ID}} int NOT NULL
)

With this solution, you would continue to add new columns as you add new entities to the database and you would delete and recreate the foreign key constraint pattern shown by @Nathan Skerl. This solution is very similar to @Nathan Skerl but looks different (up to preference).

If you are not going to have a new Table for each new Owner type then maybe it would be good to include an owner_type instead of a foreign key column for each potential Owner:

CREATE TABLE dbo.Group
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)  

CREATE TABLE dbo.User
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner_ID int NOT NULL,
    Owner_Type string NOT NULL, -- In our example, this would be "User" or "Group"
    Subject varchar(50) NULL
)

With the above method, you could add as many Owner Types as you want. Owner_ID would not have a foreign key constraint but would be used as a reference to the other tables. The downside is that you would have to look at the table to see what the owner types there are since it isn't immediately obvious based upon the schema. I would only suggest this if you don't know the owner types beforehand and they won't be linking to other tables. If you do know the owner types beforehand, I would go with a solution like @Nathan Skerl.

Sorry if I got some SQL wrong, I just threw this together.

User · Answer

The first option in  Nathan Skerl s list is what was implemented in a project I once worked with  where a similar relationship was established between three tables   One of them referenced two others  one at a time    So  the referencing table had two foreign key columns  and also it had a constraint to guarantee that exactly one table  not both  not neither  was referenced by a single row   Here s how it could look when applied to your tables   CREATE TABLE dbo  Group        ID int NOT NULL CONSTRAINT PK Group PRIMARY KEY      Name varchar 50  NOT NULL     CREATE TABLE dbo  User        ID int NOT NULL CONSTRAINT PK User PRIMARY KEY      Name varchar 50  NOT NULL     CREATE TABLE dbo Ticket       ID int NOT NULL CONSTRAINT PK Ticket PRIMARY KEY      OwnerGroup int NULL       CONSTRAINT FK Ticket Group FOREIGN KEY REFERENCES dbo  Group   ID       OwnerUser int NULL       CONSTRAINT FK Ticket User  FOREIGN KEY REFERENCES dbo  User    ID       Subject varchar 50  NULL      CONSTRAINT CK Ticket GroupUser CHECK         CASE WHEN OwnerGroup IS NULL THEN 0 ELSE 1 END         CASE WHEN OwnerUser  IS NULL THEN 0 ELSE 1 END   1            As you can see  the Ticket table has two columns  OwnerGroup and OwnerUser  both of which are nullable foreign keys   The respective columns in the other two tables are made primary keys accordingly   The CK Ticket GroupUser check constraint ensures that only one of the two foreign key columns contains a reference  the other being NULL  that s why both have to be nullable     The primary key on Ticket ID is not necessary for this particular implementation  but it definitely wouldn t harm to have one in a table like this

User · Answer

CREATE TABLE dbo OwnerType       ID int NOT NULL      Name varchar 50  NULL    insert into OwnerType  Name  values   User    insert into OwnerType  Name  values   Group      I think that would be the most general way to represent what you want instead of using a flag

User · Answer

You have a few options  all varying in  correctness  and ease of use  As always  the right design depends on your needs    You could simply create two columns in Ticket  OwnedByUserId and OwnedByGroupId  and have nullable Foreign Keys to each table  You could create M M reference tables enabling both ticket user and ticket group relationships  Perhaps in future you will want to allow a single ticket to be owned by multiple users or groups  This design does not enforce that a ticket must be owned by a single entity only  You could create a default group for every user and have tickets simply owned by either a true Group or a User s default Group    Or  my choice  model an entity that acts as a base for both Users and Groups  and have tickets owned by that entity      Heres a rough example using your posted schema   create table dbo PartyType          PartyTypeId tinyint primary key      PartyTypeName varchar 10     insert into dbo PartyType     values 1   User     2   Group      create table dbo Party       PartyId int identity 1 1  primary key      PartyTypeId tinyint references dbo PartyType PartyTypeId       unique  PartyId  PartyTypeId     CREATE TABLE dbo  Group        ID int primary key      Name varchar 50  NOT NULL      PartyTypeId as cast 2 as tinyint  persisted      foreign key  ID  PartyTypeId  references Party PartyId  PartyTypeID       CREATE TABLE dbo  User        ID int primary key      Name varchar 50  NOT NULL      PartyTypeId as cast 1 as tinyint  persisted      foreign key  ID  PartyTypeId  references Party PartyID  PartyTypeID     CREATE TABLE dbo Ticket       ID int primary key       Owner  int NOT NULL references dbo Party PartyId        Subject  varchar 50  NULL

User · Answer

Yet another option is to have  in Ticket  one column specifying the owning entity type  User or Group   second column with referenced User or Group id and NOT to use Foreign Keys but instead rely on a Trigger to enforce referential integrity   Two advantages I see here over Nathan s excellent model  above     More immediate clarity and simplicity  Simpler queries to write

[sql-server] Foreign Key to multiple tables

Examples related to sql-server

Examples related to relational-database