8

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 attributes? Are their values stable enough to be defined as primary keys or do they change very often? The values of these two properties, along with User.Username must be unique, correct?

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.

9

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:

Figure 1 - Playlist Owners IDEF1X Diagram

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.

  • i'm not seen any constraints for party is exactly one of user or group – Jasen Feb 3 at 5:00
  • It looks like your comment has to do with the following question excerpts (both contained in the section entitled “Integrity and consistency considerations”): – MDCCL Feb 9 at 21:01
  • 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—”. – MDCCL Feb 9 at 21:01
  • 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”. – MDCCL Feb 9 at 21:02
  • Therefore, once (hopefully soon) Postgres supplies, say, ASSERTIONS, I would be glad to include the applicable (declarative) constraints. For the time being, the two available DBMS-provided approaches are suggested above. – MDCCL Feb 9 at 21:03

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.