-1
  1. A user can create an event (as in physical event, such as a race or festival), this is the "coordinator".
  2. This new event can be assigned zero or more workers to work the event. They will have access to additional tools/data about the event.
  3. This event may have zero or more participants that will register through this service.
  4. Any non-registered user may find a page with details about the event

How do I create a database that efficiently stores this information?

We plan to scale to who knows how many events.

USER
-----------
id (pk)

EVENT
-----------
id (pk)
coordinator (fk, references USER.id)
page
workers
participants

workers and participants are lists of USER.ids.

I can only see it being efficient in one direction, not both. It will be very simple to query to find the workers and participants of any given event, but will be very cumbersome to find the events for any given user.

philipxy
  • 14,867
  • 6
  • 39
  • 83
TCCV
  • 3,142
  • 4
  • 25
  • 30
  • Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy May 01 '23 at 10:39

3 Answers3

2

This should actually be pretty easy to put into an RDMS. You just want a series of Many-to-Many relationships for Workers and Participants. You can also store anything else you need to in this table that is specific to that participant/worker for that event. 

User
----
ID

Events
------
ID

Participants
-----------
User ID
Event ID

Workers
-------
User ID
Event ID

If there is no other data to store with Participants and Workers, you could just use a single table and denote their status

SignUps
-------
EventID
UserID
Type ( W | P )

You could even do Coordinator this way, in case there ever becomes an opportunity for an event to have co-coordinators

Ryan Gibbons
  • 3,511
  • 31
  • 32
  • I think this is where I wanted to go. I can see that this will be much more understandable and will probably scale better as well. Thank you. – TCCV Oct 11 '11 at 03:42
2

enter image description here

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • I think this may be more valid than the first one I read. Correct me if I'm wrong, but this more clearly accounts for one person being a coordinator of one event and a participant in another. – TCCV Oct 11 '11 at 19:11
0

You can use arrays of IDs in something like MongoDB, but not (efficiently) in a SQL-based database, like SQL Server or MySQL AFAIK.

Instead, if you are not using a No-SQL based DB, use a mapping table to store the relationship between Events and Workers, and Events and Participants.

GregL
  • 37,147
  • 8
  • 62
  • 67
  • I am not familiar with Mongo or NoSQL (other than knowing that they exist). Thank you, though. – TCCV Oct 11 '11 at 03:43
  • Yeah, I'm not terribly familiar with them either. But one of the things that I liked from what I read is that it is effectively a valid datatype to have a field as an array of IDs, which is then quite easy to query. – GregL Oct 11 '11 at 04:04