CODEWITHSUNDEEP
mysqldjangodatabase-designuser-accounts
Tzach
Tzach

Reputation: 13376

Storing Email Unsubscriptions to DB

I'm working on a product that sends periodic emails to the registered customers, and I would like to implement some kind of an unsubscription mechanism from these emails.

There are about 5 types of emails and a User MySql table that contains all the users. New users are subscribed to all the email types by default, and can unsubscribe from each email type separately.

My question is how should I store these unsubscriptions to the DB, while keeping high performance and scalability and without over-complicating things. Here are a few options that came up, each has its own advantages and disadvantages:

  1. Adding a boolean column on the User table for every report type with a default value of true.
  2. Creating a new Unsubscription table with one-to-one relationship to the User table. Every email type will get a column and every user get a row.
  3. Creating a new Unsubscription table with many-to-one relationship to the User table. Every unsubscription request will create a new row on the table.

Is there a best practice for storing unsubscription information? What are the database-design issues?

Upvotes: 3

Views: 449

Answers (1)

Anentropic
Anentropic

Reputation: 33843

option 3. is the most 'normalised' in terms of db schema and means email types can be added without having to do any migrations on the db... it's also the most natural option if you already have a table for storing email types

however you will get better performance with option 1. (no JOINs) at the expense of needing to do a db migration if you add a new email type

option 2. seems to have the inflexibility of 1. while still needing a separate table so would be my least favoured option

A couple of other options to consider:

  • instead of several boolean fields on the model (option 1.) use a single BitField https://github.com/disqus/django-bitfield to represent unsubscriptions... this has the advantage that you can add new email types without migrating, plus querying is just as fast. removing types you'd have to be careful though

  • as mentioned above, if you have a table for EmailType already it makes sense to have a many-to-many relation on the User model. But you could use django-denorm to automatically update a BitField on the model which might give the best of both worlds

Upvotes: 1

Related Questions