Avoiding a many-to-many relationship cycle between 3 tables

Question

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.

Answer 1

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).