lundi 1 mai 2023

Am I bikeshedding + over-optimizing my database design by trying to account for duplicated data?

I am designing an RSVP-over-SMS system. An organizer user creates an event, and sends out SMS (text message) invitations to N amount of recipients. Each recipient will respond with a Yes/No type of answer and the system will mark their attendance accordingly.

In my database design, I have a table that holds all messages, as well as all responses. When the organizer sends a messages to User 1 of 50, the Messages table will be populated with single message record detailing the invite.

If an organizer sends a message to 50 users, should the table now include 50 message records? Every message record will contain the body of the message, which will be duplicated 50 times.

I have several ugly and un-elegant ideas in mind which solve the duplication problem, but ruin the simplicity of the schema.

My best solution so far is to create an "Invitations" table which every subsequent User response would reference through a "parent-invitation-id" field. I can foresee Organizers using the RSVP system as a simple messaging platform (i.e. sending a plain message to a user to communicate further details) and I don't think this use case fits in with my current solution.

I am confident our industry has encountered this problem before, but I am unable to trustworthy source of authority on design to consult over the competing suggestions.

I am curious to hear some input on my issue, and I'd be happy to elaborate further. Additionally, would anyone be able to point me to a trustworthy resource to consult for database design patterns, so that I could solve this kind of issue on my own?

Aucun commentaire:

Enregistrer un commentaire