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. User
s can also own any number of Group
s.
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 slug
s 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.
Best Answer
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:
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:
And four of the previous rules must be reformulated as only three:
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—):
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
norPlaylist.Slug
as columns in the DDL declarations. This is so because, in agreement with your following explanationone can conclude that their values are derivable (i.e., they must be computed or calculated in terms of the corresponding
Group.Title
andPlaylist.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 thePlaylist.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 theParty.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.