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.

Answer 1

One solution is to use record keys independent of data record. We use a GUID for each record. Foreign key constraint to one of several tables is accomplished by keeping a table of "keys" to which foreign key constraint is made. In case at point Group and User would each contain a unique key that must exist in key table, possibly accompanied by a code to indicate owning table. Ticket foreign key would reference key table. We have a parameterized stored procedure to insert records that obtains a new key value if one is not present in submitted record. New key is inserted in key table, then new record is inserted in respective table. An existing key would cause an exception on insert (should be an update.)

Answer 2

nathan_jr's 4th option (model an entity that acts as a base for both Users and Groups, and have tickets owned by that entity) doesn't enforce referential integrity on PartyId. You'd have to do that on the application layer which invites all sorts of trouble. Can't really call it an antipattern when django's genericforeignkey implements the same solution, but no doubt you can design something more robust and performant using your framework's orm (using something like django's Multi-table inheritance)

Answer 3

I have many cases like this and I just use polymorphic ability like below:

example

I have turnovers table that have this columns id, amount, user_id and I need to know the refrence of every records, So I just add two Fields table_id and table_type and my final turnovers table is like id, amount, user_id,table_id, table_type.

• if new record is about order record inserted like this [1,25000,2,22,order]
• and if new record is about increment credit like this [1,25000,2,23,credit]

note

if using M:M tables its take so much time two retrieve the records and my way

• Cons is turnovers table records number is grows up
• Pons is more flexible in new records and readable and search ability

Answer 4

you can also use an enum to identify whether Owner is user or group 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 TYPE Enum_OwnerType AS ENUM ('Group', 'User');
CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner int NOT NULL,
    OwnerType Enum_OwnerType NOT NULL,
    Subject varchar(50) NULL
)

Maybe it's no better than any of proposed solutions, it might not offer any advantage. In fact, I think that this might require altering Enum_OwnerType and even ticket in order to change OwnerType, I guess... I hope it's useful anyway.

Answer 5

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.

Answer 6

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
)

Answer 7

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.

Answer 8

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

Answer 9

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.