0

Let's say I have organization and person and they have a one-to-many relationship with post.

post could only be owned by either an organization or user (person).

What I'm doing right now is making the foreign key nullable and adding a check constraint to ensure only one of organization_id and person_id has a value and the other one is null.

Is this considered a valid SQL design (best practise)? What if I had more than two entities that could be the owner of a certain post?

enter image description here

0

You need to utilize subtypes1. Most often your situation is handled by a Party table:

CREATE TABLE PartyType
(
  PartyTypeCd  CHAR(1)      NOT NULL /* P - Person, O - Organization */
 ,Name         VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_PartyType PRIMARY KEY (PartyTypeCd)
 ,CONSTRAINT AK_PartyType UNIQUE (Name)
)

CREATE TABLE Party
(
  PartyId      INT          NOT NULL
 ,PartyTypeCd  CHAR(1)      NOT NULL
 ,Name         VARCHAR(45)  NOT NULL
 ,CONSTRAINT FK_Party_Discriminated_By_PartyType FOREIGN KEY (PartyTypeCd) REFERENCES PartyType (PartyTypeCd)
 ,CONSTRAINT PK_Party PRIMARY KEY (PartyId)
)

CREATE TABLE Person
(
  PersonId  INT          NOT NULL
 ,Bio       VARCHAR(45)  NOT NULL
 ,CONSTRAINT FK_Person_Is_Party FOREIGN KEY (PersonId) REFERENCES Party (PartyId)
 ,CONSTRAINT PK_Person PRIMARY KEY (PersonId)
)

CREATE TABLE Organization
(
  OrganizationId  INT  NOT NULL
  /* Other organization-only properties */
 ,CONSTRAINT FK_Organization_Is_Party FOREIGN KEY (OrganizationId) REFERENCES Party (PartyId)
 ,CONSTRAINT PK_Organization PRIMARY KEY (OrganizationId)
)

CREATE TABLE Post
(
  PostId   INT            NOT NULL
 ,PartyId  INT            NOT NULL
 ,Body     VARCHAR(1000)  NOT NULL
 ,CONSTRAINT FK_Post_Created_By_Party FOREIGN KEY (PartyId) REFERENCES Party (PartyId)
 ,CONSTRAINT PK_Post PRIMARY KEY (PostId)
) 

You will need to ensure the PartyTypeCd matches upon insert into the appropriate subtype table. In MySQL this must be done via a trigger.


1 You can refer to these SO posts for a more in depth treatment of the topic:

  1. https://stackoverflow.com/questions/4763141/data-modeling-supertype-subtype

  2. https://stackoverflow.com/questions/4896831/how-to-implement-referential-integrity-in-subtypes

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.