Search code examples
transactionsazure-cosmosdbrelationshipazure-cosmosdb-sqlapi

Data model for CosmosDB SQL API for limited event registration

I'm trying to create a website that allows some persons to create events (title, date, time, etc.) and others to register for and also deregister from those events. The creator of the event can specify the maximum number of attendees (e.g. maximum 10 persons for a BBQ) (most of the time between 10 and 500 persons).

I also have some requirements (from "must have" to "nice to have"):

  • Transactional safety: It shouldn't be possible that the maximum number of attendees is exceeded
  • Allow increasing or decreasing maximum number of attendees (as long as max number of attendees > current number of attendees)
  • Querying current number of attendees should be cheap (e.g. "4/10 attendees")
  • Querying events per user should be cheap (e.g. User U registered for Event E1, E2, and E3)
  • Avoid storing redundant data

I decided to give CosmosDB and its SQL API a try. I set up an "Event" container to store events in. Now I'm thinking about storing event registrations where I see the following possibilities, all with their advantages and disadvantages:

  1. Include registrations within the event itself, e.g.

    {
    "id": "b21e28e9-61c6-454a-8438-4a75e74a854b",
    "title": "BBQ",
    "date": "2022-05-17",
    "time": "17:00",
    "maxAttendees": 10,
    "attendeeIds": [
        "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357",
        "1ad88926-037d-4bf0-b50c-b380f3f5fa9f",
    ]
}
    • Advantages
    • Disadvantages
      • Deregistration is only possible using the array index of attendeeIds (again using the patch and conditional update to ensure that the index points to the correct item this might work, but it feels a bit dirty)

  2. Store registration events within the event itself (similar to an event store), e.g.

    {
    "id": "b21e28e9-61c6-454a-8438-4a75e74a854b",
    "title": "BBQ",
    "date": "2022-05-17",
    "time": "17:00",
    "maxAttendees": 10,
    "attendanceEvents": [
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "register" },
        { "userId": "1ad88926-037d-4bf0-b50c-b380f3f5fa9f", "type": "register" },
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "deregister" },
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "register" }
    ]
}
    • Advantages
      • Deregistration is easier because it's just another added operation
    • Disadvantages
      • Not sure how we would allow or deny registering a user because to get the current number of attendees we would need to consider all attendanceEvents. Even when additionally storing the current number of attendees, we would need to ensure that two contiguous "register" events are only counted as one.

  3. Store registrations in another container (e.g. "EventRegistrations") (as with typical SQL DBs):

    • Event 
      {
    "id": "b21e28e9-61c6-454a-8438-4a75e74a854b",
    "title": "BBQ",
    "date": "2022-05-17",
    "time": "17:00",
    "maxAttendees": 10,
    "currentAttendees": 3 // Optional, simplifies displaying available slots
}
    • Registration 
      {
    "id": "6b5beddc-24be-4ddf-9171-7a680093870f", // optionally eventId and userId concatenated
    "eventId": "b21e28e9-61c6-454a-8438-4a75e74a854b",
    "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357"
}
    • Advantages
      • Adding and removing registrations is easy
      • Querying the events per user is easier
    • Disadvantages
      • Adding a registration can't be made transactional because I can't read maxAttendees and write the registration in one go (right?)

Can anyone think of a way to mitigate the disadvantages of the above approaches? Or can anyone think of a totally different approach?

Solution

  • The first thing that pops up in my mind is to maybe think about the pattern: Command/Query. This means you create two models (ReadModel, WriteModel), one is for the writing which contains all the data you need, and editing it should be very easy.

    When you save the WriteModel, you can make a distilled version called the ReadModel and save it which will be prepared for rapid use. High index, flat structure. This will increase the performance very much.

    Something like:

    WriteModel:

    {
    "id": "b21e28e9-61c6-454a-8438-4a75e74a854b",
    "title": "BBQ",
    "date": "2022-05-17",
    "time": "17:00",
    "maxAttendees": 10,
    "attendanceEvents": [
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "register" },
        { "userId": "1ad88926-037d-4bf0-b50c-b380f3f5fa9f", "type": "register" },
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "deregister" },
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "register" }
    ]
}

    In the model (In my case C#) you can make all the validation

    public class EventModel
{
    public string Id {get;set;}
    public string Title {get;set;}
    public DateTime Date {get;set;}
    public int MaxAttendees {get;set;}
    public List<EventUserModel> EventUsers {get; private set;}
    
    public void AddUser(string id, string type)
    {
        if (this.EventUsers.Count >= this.MaxAttendees) {
            return;
        }
        
        this.EventUsers.Add(new EventUserModel() { Id = id, Type =type });
    }
}

public class EventUserModel {
    public string UserId {get;set;}
    public string Type {get;set;}
}

    And the ReadModel will look like this:

    public class EventReadModel
{
    private int _currentAttendees;
    public string Id {get;set;}
    public string Title {get;set;}
    public DateTime Date {get;set;}
    public int MaxAttendees {get;set;}
    public int CurrentAttendees
    {
        get => this._currentAttendees;
        set => _currentAttendees = this.EventUsers.Count;
    }
    public List<EventUserReadModel> EventUsers {get; set;}
}

public class EventUserReadModel {
    public string UserId {get;set;}
    public string FirstName {get;set;}
    public string LastName {get;set;}
    public string Email {get;set;}
    public string Type {get;set;}
}

    The ReadModel will be then used to query the data (Select) and will be updated every time you update the Model.

    {
    "id": "b21e28e9-61c6-454a-8438-4a75e74a854b",
    "title": "BBQ",
    "date": "2022-05-17",
    "time": "17:00",
    "maxAttendees": 10,
    "currentAttendees": 3,
    "attendanceEvents": [
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "register", "firstName": ... },
        { "userId": "1ad88926-037d-4bf0-b50c-b380f3f5fa9f", "type": "register", "firstName": ... },
        { "userId": "5b5a6b75-4b4e-4824-8a7b-c1d9c7783357", "type": "deregister", "firstName": ... }
    ]
}

    I hope that answers your question. Happy coding!