Yes, the identification of many-to-many (M:N for brevity) associations or relationships is a situation that a database practitioner faces quite commonly when laying out a conceptual schema. Associations of said cardinality ratios come about in business environments of very different nature, and when properly represented at the logical level by means of, e.g., a SQL-DDL arrangement, they do not introduce harmful redundancies.
In this way, the objective of a database modeling exercise should be to mirror the relevant characteristics of the business context of interest with high precision; therefore, if you identify correctly that there are numerous M:N associations, then you must express them in (a) the conceptual schema and also in (b) the respective logical-level declarations, no matter how many connections of that —or any other— kind of cardinality ratios have to be addressed.
Business rules
You have supplied a well-contextualized question, and have also clarified that the database you are working on is purely hypothetical, which is an important point since I esteem that a “real world” business scenario like the one under consideration would be much more extensive and, hence, would imply more complex informational requirements.
I decided to (1) make a few modifications and expansions to the business rules you have provided in order to (2) produce a more descriptive conceptual schema —although still rather hypothetical—. Here are some of the formulations that I put together:
- A Party1 is either a Person or an Organization
- A Party is classified by exactly-one PartyType
- A PartyType classifies zero-one-or-many Parties
- An Organization develops zero-one-or-many Products
- A Product is either a System or a Game
- A Product is classified by exactly-one ProductType
- A System is catalogued by exactly-one SystemType
- A Game can be played via one-to-many Systems
- A System is used to play one-to-many Games
- A Game is classified by zero-one-or-many Genres
- A Genre classifies zero-one-or-many Games
- A Product originates one-to-many Jobs
- A Job is fulfilled by zero-one-or-many People, who are playing the Role of Collaborators
- A Person is a Collaborator in zero-one-or-many Jobs
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 People and Organizations.
IDEF1X diagram
Subsequently, I created the IDEF1X2 diagram shown in Figure 1 (make sure to click the link to see it in a higher resolution), consolidating in a single graphical device the business rules presented above (along with some others that seem relevant):
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 based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view of data, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
As you can see, I have depicted only three M:N associations by way of the corresponding associative entity types, i.e.:
- Collaborator
- SystemGame
- GameGenre
Among other aspects, there are two distinct supertype-subtype structures, where:
Person and Organization are mutually exclusive entity subtypes of Party, their entity supertype
Product is the supertype of System and Game, which in turn are mutually exclusive subtypes
In case you are not familiar with supertype-subtype associations, you might find of help, e.g., my answers to the questions entitled:
Illustrative logical SQL-DDL layout
Successively, we must make sure that, at the logical level:
- Each entity type is represented by an individual base table
- Each single property of the applicable entity type is denoted by a particular column
- An exact data type is fixed for each column in order to ensure that all the values it contains belong to a particular and well defined set, be it INT, DATETIME, CHAR, etc. (of course, when using, e.g., Firebird or PostgreSQL, you might like to employ the more powerful DOMAINs)
- Multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined at the conceptual level
So I declared the following DDL arrangement based on the IDEF1X diagram previously shown:
CREATE TABLE PartyType ( -- Stands for an independent entity type.
PartyTypeCode CHAR(1) NOT NULL, -- To retain 'P' or 'O'.
Name CHAR(30) NOT NULL, -- To keep 'Person' or 'Organization'.
--
CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);
CREATE TABLE Party ( -- Represents an entity supertype.
PartyId INT NOT NULL,
PartyTypeCode CHAR(1) NOT NULL, -- To hold the value that indicates the type of the row denoting the complementary subtype occurrence: either 'P' for 'Person' or 'O' for 'Organization'.
CreatedDateTime TIMESTAMP NOT NULL,
--
CONSTRAINT Party_PK PRIMARY KEY (PartyId),
CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
REFERENCES PartyType (PartyTypeCode)
);
CREATE TABLE Person ( -- Denotes an entity subtype.
PersonId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
FirstName CHAR(30) NOT NULL,
LastName CHAR(30) NOT NULL,
GenderCode CHAR(3) NOT NULL,
BirthDate DATE NOT NULL,
--
CONSTRAINT Person_PK PRIMARY KEY (PersonId),
CONSTRAINT Person_AK UNIQUE (FirstName, LastName, GenderCode, BirthDate), -- Composite ALTERNATE KEY.
CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
REFERENCES Party (PartyId)
);
CREATE TABLE Organization ( -- Stands for an entity subtype.
OrganizationId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
Name CHAR(30) NOT NULL,
FoundingDate DATE NOT NULL,
--
CONSTRAINT Organization_PK PRIMARY KEY (OrganizationId),
CONSTRAINT Organization_AK UNIQUE (Name), -- Single-column ALTERNATE KEY.
CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
REFERENCES Party (PartyId)
);
CREATE TABLE ProductType ( -- Represents an independent entity type.
ProductTypeCode CHAR(1) NOT NULL, -- To enclose the values 'S' and 'G' in the corresponding rows.
Name CHAR(30) NOT NULL, -- To comprise the values 'System' and 'Person' in the respective rows.
--
CONSTRAINT ProductType_PK PRIMARY KEY (ProductTypeCode)
);
CREATE TABLE Product ( -- Denotes an entity supertype.
OrganizationId INT NOT NULL,
ProductNumber INT NOT NULL,
ProductTypeCode CHAR(1) NOT NULL, -- To keep the value that indicates the type of the row denoting the complementary subtype occurrence: either 'S' for 'System' or 'G' for 'Game'.
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT Product_PK PRIMARY KEY (OrganizationId, ProductNumber), -- Composite PRIMARY KEY.
CONSTRAINT ProductToOrganization_FK FOREIGN KEY (OrganizationId)
REFERENCES Organization (OrganizationId),
CONSTRAINT ProductToProductType_FK FOREIGN KEY (ProductTypeCode)
REFERENCES ProductType (ProductTypeCode)
);
CREATE TABLE SystemType ( -- Stands for an independent entity type.
SystemTypeCode CHAR(1) NOT NULL,
Name CHAR(30) NOT NULL,
--
CONSTRAINT SystemType_PK PRIMARY KEY (SystemTypeCode)
);
CREATE TABLE MySystem ( -- Represents a dependent entity type.
OrganizationId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
SystemNumber INT NOT NULL,
SystemTypeCode CHAR(1) NOT NULL,
ParticularColumn CHAR(30) NOT NULL,
--
CONSTRAINT System_PK PRIMARY KEY (OrganizationId, SystemNumber),
CONSTRAINT SystemToProduct_FK FOREIGN KEY (OrganizationId, SystemNumber)
REFERENCES Product (OrganizationId, ProductNumber),
CONSTRAINT SystemToSystemType_FK FOREIGN KEY (SystemTypeCode)
REFERENCES SystemType (SystemTypeCode)
);
CREATE TABLE Game ( -- Denotes an entity subtype.
OrganizationId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
GameNumber INT NOT NULL,
SpecificColumn CHAR(30) NOT NULL,
--
CONSTRAINT Game_PK PRIMARY KEY (OrganizationId, GameNumber),
CONSTRAINT GameToProduct_FK FOREIGN KEY (OrganizationId, GameNumber)
REFERENCES Product (OrganizationId, ProductNumber)
);
CREATE TABLE Genre ( -- Stands for an independent entity type.
GenreNumber INT NOT NULL,
Name CHAR(30) NOT NULL,
Description CHAR(90) NOT NULL,
--
CONSTRAINT Genre_PK PRIMARY KEY (GenreNumber),
CONSTRAINT Genre_AK1 UNIQUE (Name),
CONSTRAINT Genre_AK2 UNIQUE (Description)
);
CREATE TABLE SystemGame ( -- Represents an associative entity type or M:N association.
SystemOrganizationId INT NOT NULL,
SystemNumber INT NOT NULL,
GameOrganizationId INT NOT NULL,
GameNumber INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT SystemGame_PK PRIMARY KEY (SystemOrganizationId, SystemNumber, GameOrganizationId, GameNumber), -- Composite PRIMARY KEY.
CONSTRAINT SystemGameToSystem_FK FOREIGN KEY (SystemOrganizationId, SystemNumber) -- Multi-column FOREIGN KEY.
REFERENCES MySystem (OrganizationId, SystemNumber),
CONSTRAINT SystemGameToGame_FK FOREIGN KEY (SystemOrganizationId, GameNumber) -- Multi-column FOREIGN KEY.
REFERENCES Game (OrganizationId, GameNumber)
);
CREATE TABLE GameGenre ( -- Denotes an associative entity type or M:N association.
GameOrganizationId INT NOT NULL,
GameNumber INT NOT NULL,
GenreNumber INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT GameGenre_PK PRIMARY KEY (GameOrganizationId, GameNumber, GenreNumber), -- Composite PRIMARY KEY.
CONSTRAINT GameGenreToGame_FK FOREIGN KEY (GameOrganizationId, GameNumber)
REFERENCES Game (OrganizationId, GameNumber), -- Multi-column FOREIGN KEY.
CONSTRAINT GameGenreToGenre_FK FOREIGN KEY (GenreNumber)
REFERENCES Genre (GenreNumber)
);
CREATE TABLE Job ( -- Stands for an associative entity type or M:N association.
OrganizationId INT NOT NULL,
ProductNumber INT NOT NULL,
JobNumber INT NOT NULL,
Title CHAR(30) NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT Job_PK PRIMARY KEY (OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
CONSTRAINT Job_AK UNIQUE (Title), -- Single-column ALTERNATE KEY.
CONSTRAINT JobToProduct_FK FOREIGN KEY (OrganizationId, ProductNumber) -- Multi-column FOREIGN KEY.
REFERENCES Product (OrganizationId, ProductNumber)
);
CREATE TABLE Collaborator ( -- Represents an associative entity type or M:N association.
CollaboratorId INT NOT NULL,
OrganizationId INT NOT NULL,
ProductNumber INT NOT NULL,
JobNumber INT NOT NULL,
AssignedDateTime DATETIME NOT NULL,
--
CONSTRAINT Collaborator_PK PRIMARY KEY (CollaboratorId, OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
CONSTRAINT CollaboratorToPerson_FK FOREIGN KEY (CollaboratorId)
REFERENCES Person (PersonId),
CONSTRAINT CollaboratorToJob_FK FOREIGN KEY (OrganizationId, ProductNumber, JobNumber) -- Multi-column FOREIGN KEY.
REFERENCES Job (OrganizationId, ProductNumber, JobNumber)
);
It is opportune to stress that there are declarations of composite PRIMARY KEY constraints across several tables, which stand for the hierarchy of connections that take place among conceptual entity types, arrangement that can be very beneficial with respect to data retrieval when, e.g., expressing SELECT operations that include JOIN clauses to obtain derived tables.
Yes, (i) every M:N association and (ii) every one of the associated entity types are denoted by (iii) the corresponding table in the logical DDL structure, so pay special attention to the PRIMARY and FOREIGN KEY constraints (and the notes I left as comments) of tables representing these conceptual elements, because they assist in ensuring that the connections between the relevant rows meet the applicable cardinality ratios.
The usage of composite keys was introduced by Dr. E. F. Codd from the very origin of the relational paradigm, as demonstrated in the examples he included in his 1970 seminal paper entitled A Relational Model for Large Shared Data Banks (which, precisely, also presents the most elegant method to handle conceptual M:N associations).
I put up a db<>fiddle and a SQL Fiddle, both running on Microsoft SQL Server 2014, so that the structure can be tested “in action”.
Normalization
Normalization is a logical-level procedure that implies, basically speaking:
Eliminating non-atomic columns via first normal form so that data manipulation and constriction are much easier to cope with by the data sublanguage of use (e.g., SQL).
Getting rid of undesirable dependencies among the columns of a specific table by virtue of the successive normal forms to avoid update anomalies.
Naturally, one has to take into account the meaning carried by the table(s) and column(s) at issue.
I like to think of normalization as a test founded on science that a designer applies to the pertinent elements once he or she has delineated a stable logical-level arrangement in order to determine whether its items comply with every one of the normal forms or not. Then, if needed, the designer takes the appropriate correcting measures.
Redundancy
In the relational model, while duplication of values contained in columns is not only acceptable but expected, duplicate rows are forbidden. To that extent, as far as I can see, duplicate rows and other kinds of harmful redundancies are prevented in all the tables comprised in the logical layout exposed before, perhaps you would like to clarify your concern in this regard.
Anyway, you can certainly (a) evaluate on your own said structure by dint of the normal forms to define if it meets the requirements and (b) modify it if necessary.
Related resources
- In this series of posts I present some deliberations about a straightforward M:N association that can interrelate the instances of two different entity types.
- In this other one I propose an approach to handle an occurrence of the “Bill of Materials” or “Parts Explosion” construct, in which I describe how to connect distinct instances of the same type of entity.
Ternary associations
There is another important aspect that you brought up via comments (posted in an now-deleted answer):
Every time I try to make a bridge the elements in that bridge also have a Many to Many, I'm under the impression that isn't allowed or at least discouraged.
That circumstance seems to indicate that one of your concerns has to do with conceptual ternary associations. Basically speaking, this sort of associations comes about when there exists (1) a relationship involving (2) two other relationships, in other words “a relationship between relationships” —a typical situation too, since a relationship is an entity in its own right—.
These arrangements, when suitably managed, do not bring harmful redundancies either. And, yes, if there is a certain use case where you identify that such relationships present themselves among “real world” entity types, you have to (i) model and (ii) declare them with accuracy at the logical level.
- Here is a question and answer were we analyze a domain of discourse about surveys, which includes an example of a ternary association.
- In this very good answer, @Ypercube presents a diagram and the respective DDL structure for an interesting diamond shaped relationship, which is very similar to this class of scenarios.
