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 an independent entity type.
PlaylistId INT NOT NULL,
OwnerId INT NOT NULL,
Title CHAR(30) NOT NULL,
CreatedDateTime TIMESTAMP NOT NULL,
--
CONSTRAINT Playlist_PK PRIMARY KEY (PlaylistId),
CONSTRAINT Playlist_AK UNIQUE (Title), -- ALTERNATE KEY.
CONSTRAINT PartyToParty_FK FOREIGN KEY (OwnerId) -- Establishes the relationship with (a) the supertype and (b) through the subtype with (c) the subtypes.
REFERENCES Party (PartyId)
);
CREATE TABLE GroupMember ( -- Denotes an associative entity type.
MemberId INT NOT NULL,
GroupId INT NOT NULL,
IsOwner BOOLEAN NOT NULL,
JoinedDateTime TIMESTAMP NOT NULL,
--
CONSTRAINT GroupMember_PK PRIMARY KEY (MemberId, GroupId), -- Composite PRIMARY KEY.
CONSTRAINT GroupMemberToUserProfile_FK FOREIGN KEY (MemberId)
REFERENCES UserProfile (UserId),
CONSTRAINT GroupMemberToMyGroup_FK FOREIGN KEY (GroupId)
REFERENCES MyGroup (GroupId)
);
Of course, you can make one or more adjustments so that all the characteristics of your business context are represented with the needed precision in the actual database.
Note: I have tested the above logical layout on this db<>fiddle and also on this SQL Fiddle, both “running” on PostgreSQL 9.6, so that you can see them “in action”.
The Slugs
As you can see, I did not include Group.Slug nor Playlist.Slug as columns in the DDL declarations. This is so because, in agreement with your following explanation
These slugs are unique, lowercase, hyphenated versions of their respective entity's title. For example, a group with the title 'Test Group' would have the slug 'test-group'. Duplicates are appended with incremental integers. This would change any time their title changes. I believe that means they wouldn't make great primary keys? Yes, slugs and usernames are unique in their respective tables.
one can conclude that their values are derivable (i.e., they must be computed or calculated in terms of the corresponding Group.Title and Playlist.Title values, sometimes in conjunction with —I assume, some kind of system-generated— INTEGERs), so I would not declare said columns in any of the base tables as they would introduce update irregularities.
In contrast, I would produce the Slugs
maybe, in a view, which (a) includes the derivation of such values in virtual columns and (b) can be used directly in further SELECT operations —appending the INTEGER part could be obtained, e.g., by combining the value of (1) the Playlist.OwnerId with (2) the intermediate hyphens and (3) the value of the Playlist.Title;
or, by virtue of application program code, mimicking the approach described before (perhaps procedurally), once the pertinent data sets are retrieved and formatted for end-user interpretation.
In this manner, any of those two methods would avoid the “update synchronization” mechanism that ought to be put in place iff the Slugs are retained in columns of base tables.
Integrity and consistency considerations
It is critical to mention that (i) each Party row must be complemented at all times by (ii) the respective counterpart in exactly one of the tables standing for the subtypes, which (iii) must “comply with” the value contained in the Party.PartyTypeCode column —denoting the discriminator—.
It would be quite advantageous to enforce that kind of situation in a declarative manner, but none of the major SQL database management systems (including Postgres) has supplied the necessary instruments to proceed like that; therefore, writing procedural code within ACID TRANSACTIONS is so far the best option to guarantee that the circumstances previously described are always met in your database. Other possibility would be resorting to TRIGGERS, but they are prone to make things untidy, so to speak.
Comparable cases
If you want to establish some analogies, you might be interested in taking a look at my answers to the (newer) questions entitled
since comparable scenarios are discussed.
Endnotes
1 Party is a term used in legal contexts when referring to either an individual or a group of individuals that compose a single entity, so this denomination is suitable to represent the concepts of User and Group with respect to the business environment in question.
2 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
