patternModerate

Designing a database for a video game business domain with multiple many-to-many relationships

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

businesswithdesigningdatabasevideogameformultiplemanyrelationships

Problem

I am relatively new to database design, and I decided to make my own hypothetical database for practice. However, I am having trouble modeling and normalizing it, as I esteem that there are numerous many-to-many (M:N) relationships.
General scenario description

The database is meant to retain data about various People that have worked on the Zelda series. I want to keep track of the Console(s) that a Game can be played on, Employees that have had a part in the Games development, the Jobs the Employee had (many Employees worked on different Jobs across multiple Games), etc.
Business rules

Multiple Employees can work on multiple Games.

Multiple Games can be on the same Console.

Multiple Consoles can be a platform for the same Game.

Multiple Employees can have the same Job.

An Employee can have multiple Jobs.

A Game can have multiple Employees.

A Game can have multiple types of Jobs in it's development

Multiple Games can have the same type of Job attached.

A Console can have multiple People working on it.

A Person can work on multiple Consoles.

Attribute names and sample values

Employee Name, which can be split into First and Last (for example “John” and “Doe” )

Game Title (for example “Ocarina of Time”)

Job Title (for example “Level Design”, “Director”, “Composure”, “Level Designer”, “Programmer”, “Localization”, etc.).

Console Name (for example “Game Boy Advance”)

The issue

So far, it seems no matter what I design there are data redundancies and M:N relationships between the entity types of interest everywhere. However I feel that database designers must run into this kind of problem all the time, so there must be a solution.

Note: I am well able to find the data to fill the table, the problem is organizing it into a database with tables in a normalized form.

Solution

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:

“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”

“How to model an entity type that can have different sets of attributes?”

“Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money”

“Modelling a database structure for multiple user types and their contact information”

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 p

Code Snippets

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 a

Context

StackExchange Database Administrators Q#140534, answer score: 18

Revisions (0)

No revisions yet.