(ID),ĬASE WHEN OwnerGroup IS NULL THEN 0 ELSE 1 END +ĬASE WHEN OwnerUser IS NULL THEN 0 ELSE 1 END = 1Īs you can see, the Ticket table has two columns, OwnerGroup and OwnerUser, both of which are nullable foreign keys. (ID),ĬONSTRAINT FK_Ticket_User FOREIGN KEY REFERENCES dbo. ID int NOT NULL CONSTRAINT PK_Ticket PRIMARY KEY,ĬONSTRAINT FK_Ticket_Group FOREIGN KEY REFERENCES dbo. ID int NOT NULL CONSTRAINT PK_User PRIMARY KEY, ID int NOT NULL CONSTRAINT PK_Group PRIMARY KEY, Here's how it could look when applied to your tables: CREATE TABLE dbo. 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. (One of them referenced two others, one at a time.) The first option in Skerl's list is what was implemented in a project I once worked with, where a similar relationship was established between three tables. int NOT NULL references dbo.Party(PartyId), PartyTypeId as cast(1 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID) PartyTypeId as cast(2 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID)
PartyTypeId tinyint references dbo.PartyType(PartyTypeId), Heres a rough example using your posted schema: create table dbo.PartyType Or (my choice) model an entity that acts as a base for both Users and Groups, and have tickets owned by that entity. 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. 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 M:M reference tables enabling both ticket:user and ticket:group relationships. You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table. As always, the right design depends on your needs.
You have a few options, all varying in "correctness" and ease of use.
0 kommentar(er)
