How to design a database in rails where all tables can be filtered by a global setting? Default global scope?

Question

I am building a rails app and the data should be reset every "season" but still kept. In other words, the only data retrieved from any table should be for the current season but if you want to access previous seasons, you can.

We basically need to have multiple instances of the entire database, one for each season.

The clients idea was to export the database at the end of the season and save it, then start fresh. The problem with this is that we can't look at all of the data at once.

The only idea I have is to add a season_id column to every model. But in this scenario, every query would need to have where(season_id: CURRENT_SEASON). Should I just make this a default scope for every model?

Is there a good way to do this?

Answer 1

If you want all the data in a single database, then you'll have to filter it, so you're on the right track. This is totally fine, as data is filtered all the time anyway so it's not a big deal. Also, what you're describing sounds very similar to marking data as archived (where anything not in the current season is essentially archived), something that is very commonly done and usually accomplished (I believe) via setting a boolean flag on every record to true or false in order to hide it, or some equivalent method.

You'll probably want a scope or default_scope, where the main downside of a default_scope is that you must use .unscoped in all places where you want to access data outside of the current season, whereas not using a default scope means you must specify the scope on every call. Default scopes can also seem to get applied in funny places from time to time, and in my experience I prefer to always be explicit about the scopes I'm using (i.e. I therefore never use default_scope), but this is more of a personal preference.

In terms of how to design the database you can either add the boolean flag for every record that tells whether or not that data is in the current season, or as you noted you can include a season_id that will be checked against the current season ID and filter it that way. Either way, a scope of some sort would be a good way to do it.

If using a simple boolean, then either at the end of the current season or the start of the new season, you would have to go and mark any current season records as no longer current. This may require a rake task or something similar to make this convenient, but adds a small amount of maintenance.

If using a season_id plus a constant in the code to indicate which season is current (perhaps via a config file) it would be easier to mark things as the current season since no DB updates will be required from season to season.

Answer 2

[Disclaimer: I'm not familiar with Ruby so I'll just comment from the database perspective.]

The problem with this is that we can't look at all of the data at once.

If you need to keep the old versions accessible, then you should keep them in the same database.

Designing "versioned" (or "temporal" or "historized") data model is something of a black art - let me know how your model looks like now and I might have some suggestions how to "version" it. Things can get especially complicated when handling connections between versioned objects.

In the meantime, take a look at this post, for an example of one such model (unrelated to your domain, but hopefully providing some ideas).

Alternatively, you could try using a DBMS-specific mechanism such as Oracle's flashback query, but this is obviously not available to everybody and may not be suitable for keeping the permanent history...