Database schema for entities with two possible owner/parent types?

I'm using PostgreSQL with Sequelize as my ORM.

I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also own any number of Groups.

My third type, Playlist, can belong to either a User OR a group. What would be the best way to design a schema for this type so that it can have either one type of owner or the either?

My first pass I created both associations, but only populated one at a time. This could possibly work, but seems hacky and makes queries difficult.

Additional information

Here are my responses to the clarification requests posted by MDCCL via comments:


(1) If a Playlist is owned by a given Group, one can say that this Playlist is related to one-to-many Users, as long as they are Members of such Group, right?

I believe this is technically true, but this one-to-many association does not exist explicitly.


(2) So, is it possible for a specific Playlist to be owned by one-to-many Groups at the same time?

No, it should not be possible for a Playlist to be owned by one-to-many Groups.


(3) Is it possible for a particular Playlist to be owned by one-to-many Groups and, at the same time, by one-to-many Users who are not Members of such Group?

No, because as in (2) a one-to-many from Playlist to Group should not exist. Additionally, if a Playlist is owned by a Group it is not owned by a User, and vice versa. Only one owner at a time.


(4) What are the properties used to uniquely identify a Group, a User and a Playlist?

They each have a surrogate primary key (id), as well as a natural key (though not primary). These are slug for Group and Playlist, and username for User.


(5) Could a particular Playlist suffer an Owner change?

Although I don't plan on this being a feature (at least initially), I suppose this could hypothetically occur.


(6) What’s the meaning of the Group.Slug and Playlist.Slug attr

If I understand your specifications properly, your scenario involves —among other significant aspects— a supertype-subtype structure.

I will exemplify below how to (1) model it at the conceptual level of abstraction and subsequently (2) represent it in a logical-level DDL design.
Business rules

The following conceptual formulations are among the most important rules in your business context:

• A Playlist is owned by either exactly one Group or exactly one User at a specific point in time

• A Playlist may be owned by one-to-many Groups or Users at distinct points in time

• A User owns zero-one-or-many Playlists

• A Groups owns zero-one-or-many Playlists

• A Group is made up of one-to-many Members (who must be Users)

• A User may be a Member of zero-one-or-many Groups.

• A Group is made up of one-to-many Members (who must be Users)

As the associations or relationships (a) between User and Playlist and (b) between Group and Playlist are quite alike, this fact reveals that User and Group are mutually exclusive entity subtypes of Party1, which is in turn their entity supertype —supertype-subtype clusters are classic data structures that come about in conceptual schemas of very diverse sorts—. In this manner, two new rules can be asserted:

• A Party is categorized by exactly one PartyType

• A Party is either a Group or a User

And four of the previous rules must be reformulated as only three:

• A Playlist is owned by exactly one Party at a specific point in time

• A Playlist may be owned by one-to-many Parties at distinct points in time

• A Party owns zero-one-or-many Playlists

Expository IDEF1X diagram

The IDEF1X2 diagram shown in Figure 1 consolidates all of the aforementioned business rules along with other ones that appear pertinent:

As demonstrated, Group and User are portrayed as subtypes that are connected by the respective lines and the exclusive symbol with Party, the supertype.

The Party.PartyTypeCode property stands for the subtype discriminator, i.e., it indicates which kind of subtype instance must supplement a given supertype occurrence.

Also, Party is connected with Playlist via the OwnerId property which is depicted as a FOREIGN KEY that points to Party.PartyId. In this way, Party interrelates (a) Playlist with (b) Group and (c) User.

Accordingly, since a particular Party instance is either a Group or a User, a specific Playlist can be linked with at most one subtype occurrence.
Illustrative logical-level layout

The IDEF1X diagram expounded before has served me as a platform to create the following logical SQL-DDL arrangement (and I have supplied notes as comments highlighting several points of particular relevance —e.g., the constraint declarations—):

```
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE PartyType ( -- Represents an independent entity type.
PartyTypeCode CHAR(1) NOT NULL,
Name CHAR(30) NOT NULL,
--
CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode),
CONSTRAINT PartyType_AK UNIQUE (Name)
);

CREATE TABLE Party ( -- Stands for the supertype.
PartyId INT NOT NULL,
PartyTypeCode CHAR(1) NOT NULL, -- Symbolizes the discriminator.
CreatedDateTime TIMESTAMP NOT NULL,
--
CONSTRAINT Party_PK PRIMARY KEY (PartyId),
CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE UserProfile ( -- Denotes one of the subtypes.
UserId INT NOT NULL, -- To be constrained as both (a) the PRIMARY KEY and (b) a FOREIGN KEY.
UserName CHAR(30) NOT NULL,
FirstName CHAR(30) NOT NULL,
LastName CHAR(30) NOT NULL,
GenderCode CHAR(3) NOT NULL,
BirthDate DATE NOT NULL,
--
CONSTRAINT UserProfile_PK PRIMARY KEY (UserId),
CONSTRAINT UserProfile_AK1 UNIQUE ( -- Multi-column ALTERNATE KEY.
FirstName,
LastName,
GenderCode,
BirthDate
),
CONSTRAINT UserProfile_AK2 UNIQUE (UserName), -- Single-column ALTERNATE KEY.
CONSTRAINT UserProfileToParty_FK FOREIGN KEY (UserId)
REFERENCES Party (PartyId)
);

CREATE TABLE MyGroup ( -- Represents the other subtype.
GroupId INT NOT NULL, -- To be constrained as both (a) the PRIMARY KEY and (b) a FOREIGN KEY.
Title CHAR(30) NOT NULL,
--
CONSTRAINT Group_PK PRIMARY KEY (GroupId),
CONSTRAINT Group_AK UNIQUE (Title), -- ALTERNATE KEY.
CONSTRAINT GroupToParty_FK FOREIGN KEY (GroupId)
REFERENCES Party (PartyId)
);

CREATE TABLE Playlist ( -- Stands for a

StackExchange Database Administrators Q#107919, answer score: 10