Data model conundrum

rubyonrails-talk
1

I’ve been mulling over a question of how best to organise the model/data for a certain specific kind of problem. I wonder if there are any good design patterns out there for this that I don’t know of.

Say, a user has several settings, and each setting has the same 2 or 3 attributes related to it:

  • x_newsletter_opt_in_date
  • x_newsletter_opt_in_reason
  • y_newsletter_opt_in_date
  • y_newsletter_opt_in_reason
  • z_newsletter_opt_in_date
  • z_newsletter_opt_in_reason
  • a_print_letter_opt_in_date
  • a_print_letter_opt_in_reason

etc

We want to be able to ask at any given moment if the user has the given opt-in, and if necessary look up the reason for the opt-in.

On the one hand, the repetition in one dimension seems to point towards a single model/table in a has_many/ belongs_to relation with users, for example called Setting, with setting_name, opt_in and reason columns. However, this would mean a structural ambiguity: since there can in principle be an indeterminate number of settings for the given user, they may have an opt_in for the given setting, or they may just not have the setting at all. At the interface level this leads to various related conundrums.

On the other hand, the fact that we want to know unambiguously about a definite set of settings seems to point towards a model that represents all settings in a single instance/row, so, say, Settings, which has these as attributes:

  • x_newsletter_opt_in_date
  • x_newsletter_opt_in_reason
  • y_newsletter_opt_in_date
  • y_newsletter_opt_in_reason
  • z_newsletter_opt_in_date
  • z_newsletter_opt_in_reason
  • a_print_letter_opt_in_date
  • a_print_letter_opt_in_reason

… but the latter seems like a horrible kludge.

I flip-flop between these two seemingly unsatisfactory solutions. Is there a better 3rd option?

1 Like
2

The fact that the example uses several newsletters suggests to me they come and go. If that was the case, a change in them would require a database migration. At first sight, feels too rigid to me.

The first option seems more natural in that sense. Maybe you can provide a uniform access with an ad-hoc API at the model/decorator level that ensures the caller sees a consistent view of settings, present or missing.

Depends on details and context you only know, throwing just 2 cents in case it help :).

2 Likes
3

The repeated *_opt_in_date and *_opt_in_reason fields feel like an OptIn model trying to get out.

Multiple ways to model the data layer:

  1. Specific opt_ins table
  2. Generalised settings table, perhaps with JSON metadata
  3. Serialise opt-ins as an array or JSON on users and access as a PORO
2 Likes
4

Yes, I think you’re right. I guess that’s the key question really: what that intermediate level should look like if one goes down this road. Might it also be something to do with a form class perhaps?

The other solution I’m wondering about is putting all this stuff in a JSONB field and then ensuring some coherent structure to this JSON with something like Dry::Struct or JSON schemas. That seems to deal with the awkward mix between flexibility and coherent structure implied here. The problem may be though, that this could end up requiring individual indices to be added for each setting, which then means you’re writing a migration for every change…