MySQL Database Design – Avoiding Many-to-Many Relationship Cycles

database-designmany-to-manyMySQL

I'm designing a database for a IDLE CPU time donation system. and I struggled to design the database.

The problem is the following.
I have 3 entities (tables):

donors: hold donors' information
devices: a big array of various machine types (Phones, PCs, Tablets…)
events: hold information about the events, that will receive the CPU donations.

The rules the database is based on are.

Each donor owns many personal devices.
A donor can register to events.
By registering to an event, the donor choose which device(s) to donate CPU time from.
A donor can register to as many events he chose to.
A donor can register a device to multiple events.

At first, I created 2 additional tables besides the ones listed above(donors, devices, events):

donor-device : this table contains the various devices (Phone, PC, TV …) owned by clients.
donor-event: this table contains the donors registered in events and the status of their participation (pending, rejected, accepted).

Then I discovered that this data model is not relevant in my use case. in this case I cannot know the devices that the donor chooses to donate for a specific event.

Here are some solutions I thought of, though I'm not convinced of their optimality.

Adding a column in the client-event table containing a string with the id of the devices donated + the amount of CPU time donated.
Using a JSON field instead if the string.
Adding a third many-to-many relationship device-event-client.

PS: I'm not tied to a relational solution, I can try other models as well.

Best Answer

A way to solve your problem is to think of registrations as entities: a registration has a donor, an event, and a set of devices.

The relationship between donors and registrations has cardinality 1:N (a donor can have more registrations, a registration concerns a unique donor), the relationship between events and registrations has cardinality 1:N as well, while the relationship between registration and devices is M:N (in a registration, a donor can offer various devices, while a device can be registered multiple times for different events).

So, this situation can be represented with 5 tables:

Donors(DonorID, other attributes)
Devices(DeviceID, other attributes, OwnerID)
Events(EventID, other attributes)
Registrations(RegID, DonorID, EventID, maybe other attributes, like Status, Date, etc.)
RegistrationsDevices(RegID, DeviceID, maybe other attributes as TimeDonated, etc.)

with DonorID primary key for Donors, DeviceID primary key for Devices, OwnerIDforeign key referring to a Donor, EventID primary key for Events, RegID primary key for Registrations (that has DonorID and EventID as foreign keys), and (RegID, DeviceID) primary key for RegistrationsDevices (and also foreign keys).

Related Solutions

Database Design – Understanding Many-to-Many Relationships

I think your first design is better with two additional table highlights and bookmarks.

In long run it will be difficult with design to save data in JSON format, Let say you need to search a bookmark or search a highlights, I am thinking what would be sql query for that. or Let say for page you need to show the highlights, How you will show that.

Even if somehow you solve above problem, performance issue will be a concern. The first design offers indexes and even with long run big tables data can fetched quickly.

Check the datatype of id column in table books_users, it would be INT,

Database Design – Joining Three Tables in a Many-to-Many Relationship

I was just going to make this a comment but it is too long to just be a comment, and I think it is one viable solution though I would say that there are many solutions and it will depend on how large the tables are going to be, how frequently they are used, how they are used, etc...as to what is going to be the best for your situation.

In this case, I think having your Type, Status and SubStatus tables all be separate and having a table that links them together, seems to make sense.

Here is the code that I put together to make that happen for your data.

create table tblType
(
    TypeID int identity(1,1) primary key clustered
    ,TypeName varchar(20)
);
GO
Create table tblStatus
(
    StatusID int identity(1,1) primary key clustered
    ,StatusName varchar(20)
);
GO
Create table tblSubStatus
(
    SubStatusID int identity(1,1) primary key clustered
    ,SubStatusName varchar(20)
);
GO
create table StatusSubStatus
(
    StatusSubStatusID int identity(1,1) primary key clustered
    ,TypeID int
    ,StatusID int
    ,SubStatusID int
);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Type 
FOREIGN KEY (TypeID) REFERENCES tblType(TypeID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Status 
FOREIGN KEY (StatusID) REFERENCES tblStatus(StatusID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_SubStatus 
FOREIGN KEY (SubStatusID) REFERENCES StatusSubStatus(StatusSubStatusID);
GO
insert into tblType 
Values ('Type 1'), ('Type2'); --id 1, 2
GO
insert into tblStatus 
Values ('A'); --id 1
GO
insert into tblSubStatus 
Values ('X'), ('Y'), ('Z');  --id's 1, 2, 3 respectively
GO
insert into StatusSubStatus
Values (1, 1, 1), (1, 1, 2), (2, 1, 3);
GO

The advantage to this is that you get to keep your Status/Type tables from being large. The disadvantage is that you have a linking table that can get quite large. But it is a clean way to get all of this stuff together cleanly.

If you wanted to see all of the data for TypeID of 1 you would just do the query

Select TypeName, StatusName, SubStatusName
from StatusSubStatus sss
join tblType tt on sss.TypeID = tt.TypeID
join tblStatus ts on sss.StatusID = ts.StatusID
join tblSubStatus tss on tss.SubStatusID = sss.SubStatusID
Where tt.TypeID = 1;

There are many solutions to making this work, this is one that I like but it will certainly depend on your situation as to whether this is the best one for you or not.

Best Answer

Related Solutions

Database Design – Understanding Many-to-Many Relationships

Database Design – Joining Three Tables in a Many-to-Many Relationship

Related Question