0

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.

0

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

| improve this answer | |
  • Thank you for you response. This quite what I was looking for except, I need the table DonorsDevices because I need to query devices owned by donors separately. for example many Donors can own a PC but the brand/CPU frequency and architecture can defer. Should I just add a 6th table or is there a better way. – M. Iduoad Apr 14 '19 at 22:28
  • Can you solve this by adding to Devices a foreign key for Donors? In this case you can omit DonorID inside Registration (since this information is carried by DeviceID inside RegistrationsDevices). – Renzo Apr 15 '19 at 6:32
  • Unfortunately, I'll have to the other way around. I think I'll have to add a foreign key for Devices in Donors. – M. Iduoad Apr 15 '19 at 10:15
  • In your question you said that each donor has several devices. So you can't put a foreign key in donor, otherwise you should have multiple value. Why can't you put a foreign key in device? A device can be shared by several donors? – Renzo Apr 15 '19 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.