1

We have a Postgres DB in which we store a "settings" JSON field on Users. This JSON field indicates (among other things) whether the user is subscribed to a digest we send out. There's some model validation that ensures that values in this key / value pair operate like an enum.

We're going to try an experiment in which we auto-subscribe users who visit the site (we know their email addresses because we're a small network).

We are debating how the user's subscription state should be modeled in the DB. One team member thinks the DB should reflect the user's actions, so the "subscribed" value in the JSON should remain NULL until the user explicitly changes it, and we should send emails to users who have "subscribed" == NULL and who have visited the site. Another team member thinks the DB should reflect the user's subscription state, so the "subscribed" value in the JSON should be set to true once the user visits the site for the first time.

Which philosophy do others in this forum think is more common and/or intuitive?

1
  • If the question is: should the data be normalized into a column in a table in the database vs continued to be read from and written to in JSON, well the purpose of a database (particularly a relational one like PostgreSQL) is to store data in a normalized form. JSON is denormalized and a little bit of anti-pattern to that purpose. But not sure if that's what you're asking?
    – J.D.
    Dec 13, 2023 at 19:49

3 Answers 3

1

We're going to try an experiment in which we auto-subscribe users who visit the site ...

Leaving aside the database issue, just for a moment ...

Do you have the User's informed consent to do this?
Does your Privacy Notice say that you might send them things and, at the point they "visit" your site, have they accepted that Privacy Notice?

This Reddit post asked about much the same thing:

... a statement for the registration page explaining that, by creating an account, the user is also subscribing to three email lists ...

(emphasis mine)

A reply to this post includes a link to the Information Commissioners Office (ICO) web site, which fairly concisely slams the door on this whole idea, with statements including these:

Consent requires a positive opt-in. Don’t use pre-ticked boxes or any other method of default consent.

and

Avoid making consent to processing a precondition of a service.

In short, under the General Data Protection Regulations (GDPR), you cannot assume consent. The User must give it, explicitly and positively.

OK, leaving "Legal Land" for "Database World" once again ...

NULL cannot represent any concrete value or state, by definition:

... a special marker used to indicate that a data value does not exist ...

That's the whole point of it. It represents the absence of any known value. Regarding NULL as "don't know" is a safer starting point.
Given that users must "opt-in" to a subscription, you should store a concrete value when they do. No value, no subscription.
Alternatively, you could create a linked "subscription" record for the user when they opt-in. The absence ("not subscribed") and presence ("subscribed") of that record is meaningful.

On a more technical note, NULL values are generally excluded from indexes on fields, which can make finding records containing NULL's painfully slow.
This is where the linked table approach is more economical. You only send emails to users with records in that table. You could even support multiple subscriptions by entries in the same table, keyed by User and Subscription IDs.

1
  • thank you, these comments are great to read :)
    – duhaime
    Dec 29, 2023 at 1:08
0

Leaving aside the question of why this data is being persisted to the database as JSON rather than a separate relational column which would be usual practice...

My first question would be what happens if you decide in several months to reverse the decision and to require opt-in again. If you have only the one attribute and you have updated every row where subscribed IS NULL to subscribed = 'Y', you don't have the information necessary to unsubscribe everyone you opted in without unsubscribing those that opted in themselves. So from that standpoint alone, my bias would be to leave the data unchanged and send emails to everyone where subscribed IS NULL. If you did want to flip the subscribed attribute, you'd likely need to add another attribute like opt-in to the JSON to capture whether the individual had opted-in in order to allow you to reverse the experiment. But that would mean managing two separate but strongly related attributes which increases the potential for data discrepancies (i.e. if the front-end incorrectly allows a row to have opt-in = 'Y' and subscribed = 'N', that row wouldn't make sense).

0

Which philosophy do others in this forum think is more common and/or intuitive?

I think this is about how you run your application and specifically your business - not the database design. It is the application design. This is about whether some data need to be stored or not - based upon some user action. How the data is to be stored is not an issue.

The options are not uncommon. We come across similar functionality often as we visit and signup on new websites. In general, I prefer that I am not subscribed automatically. I want to explore and be free to know what features the website offers me. And, when I feel comfortable, I may opt for a regular subscription.

I will give an example of a recent experience. I attended a meetup on a technology I am new to. After the event (the event organizer has my email) I started to get emails regularly offering services and more details about the product, etc. Is this acceptable to me? It is a personal preference. I prefer not to be subscribed automatically. I did opt out of it eventually after half a dozen emails.

Sometimes, I don't get such subscription automatically. I just get one offer for subscription and some product details. This is what I prefer.

There may be other ways to handle this. As mentioned by you, send occasional reminders to enroll into subscription, along with promotions, etc., and not use automatic subscription.

You may want to try both options and see which works better in your case. You can even run a poll with your subscribers what they prefer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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