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.