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.

Idiolect

To my mind every ticket is owned by a group. It is just that a user is a group of one. Which choice 4 from @nathan-skerl models. If you use Guids as keys the whole thing also works quite well – Chelton

Google re sql/database subtyping/inheritance. – Meteorograph

Answer 1

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
)

Chesterfieldian

What would a query for User/Group tickets looks like? Thanks. – Omnibus

What's the benefit of the persisted computed columns in Group and User tables? The primary key in Party table already ensures that there will be no overlap in Group Ids and User Ids, so the foreign key only needs to be on the PartyId alone. Any queries written would still need to know the tables from the PartyTypeName anyway. – Electrodynamic

@ArinTaylor the persisted column prevents us from creating a Party of type User and relating it to a record in dbo.Group. – Chesterfieldian

I would add one thing here. And this actually helps a lot in generic application framework development. Since you have a "Name" property in both group and user, you should actually move that field in the base type. Very good example otherwise! – Underhanded

@NathanSkerl Can you please tell me what I am missing here. A use case would be to identify what the name of the ticket owner is. So I attempted to drill up from the ticket and get nowhere when I hit the Party table. For example: A ticket has an OwnerId (PartyId) = 5. Now lets go to the Party table WHERE PartyId=5. From here how would I get to the Name of the User or Group of the ticket? Wouldn't the User/Group tables need a PartyId assigned to them? SELECT t.*, u.Name FROM Ticket t INNER JOIN Party p ON t.PartyId = p.Id LEFT OUTER JOIN User u ON p.PartyId = u.PartyId. – Anzus

@BlakeRivell yes, the party is either a User or a Group (never both), depending on the PartyTypeId. The ID column in both User and Group tables is a PartyId – Chesterfieldian

@Omnibus I know this is an old question but the query would be something like SELECT t.Subject AS ticketSubject, CASE WHEN u.Name IS NOT NULL THEN u.Name ELSE g.Name END AS ticketOwnerName FROM Ticket t INNER JOIN Party p ON t.Owner=p.PartyId LEFT OUTER JOIN User u ON u.ID=p.PartyId LEFT OUTER JOIN Group g on g.ID=p.PartyID; In the result you would have every ticket subject and owner name. – Nappie

Very complete answer, the example is interesting and very general. One small remark though : you don't need the : unique (PartyId, PartyTypeId) as PartyId is already primary key – Dispute

Regarding option 4, Can someone confirm whether this is an anti pattern or a solution for an anti pattern? – Easement

@NathanSkerl There is one problem with this solution: A row can be added to the Party table without adding a row to any of User or Group table. A simple way to avoid this is adding 2 nullable foreign keys in Party and adding necessary checks. But then your first option would be better. So, any better way to do this using option 4? – Bechtold

how would you do the same thing in postgres? I cant find a way to persist column value in add table query. – Maestoso

Answer 2

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

Alroy

This is also what we have in our software and I would avoid if you are trying to create a generic data access framework. This design will increase complexity in the app layer. – Underhanded

I am really new to SQL so correct me if this is wrong, but this design seems to be an approach to use when you are extremely confident that you will only need two owner types of a ticket. Down the road if a third ticket owner type were introduced, you would have to add a third nullable foreign key column to the table. – Fact

@Shadoninja: You are not wrong. In fact, I think that's a completely fair way of putting it. I'm generally okay with this kind of solution where it's justified, but it certainly wouldn't be first on my mind when considering options – precisely because of the reason you've outlined. – Alroy

@Underhanded In this case you can use a unique foreign key based on two columns RefID, RefType where RefType is a fixed identifier of the target table. If you need integrity you can do checks in the trigger or app layer. Generic retrieval is possible in this case. SQL should allow FK definition like this, making our lives easier. – Nedanedda

Answer 3

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.

Maccabean

Answer 4

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.

Paralogism

But this wouldn't allow for a foreign key right? I'm still trying to figure out the right design for my current project, where one table can reference at least 3 maybe more in the future – Typesetter

Isn't that going ot hurt multiuser performance given that the triggers will need to lock tables to reliably perform their checks? – Probably

I am also thinking of the same solution to apply in our project. I am not sure if it is perfect, but since I expected in the future to have more than just 2 tables that vary, I prefer this over having NULL columns. – Flavorous

Answer 5

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.

Trachytic

Answer 6

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

Cicily

Answer 7

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)

Molding

Answer 8

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

Megrims

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.

Emporium