3

I'm building a Rails app that will have a very high number of models using single-table inheritance. For the model subclasses, I want to be able to set constant values for things like name and description. I know I can set defaults using attribute, like this:

class SpecialWidget < Widget
  attribute :name, :string, default: "Special Widget"
  attribute :description, :text, default: "This is an awfully important widget."
end

The advantage here, as I understand it, is that by storing the defaults in the database, I retain the ability to do things use #order to sort by name, and paginate. But it seems bad to store constants in the database like that. It seems better to use constant methods, like this:

class SpecialWidget < Widget
  def name
    "Special Widget"
  end

  def description
    "This is an awfully important widget."
  end
end

In fact, that's what I was doing originally, but then I read posts like these (one, two, three), which pointed out that then if I wanted to do nice things like sort by the methods, I'd have to load the entire Widget.all into memory and then do a plain-old Ruby sort.

My application is built quite heavily around these STI models, and I will definitely have to sort by constants like name. Are the concerns about sorting and pagination significant disadvantages that will cause me to come to regret using methods in the future, or will the difference be negligible? What other disadvantages/problems might I have? I'd really like to be able to use methods instead of storing constants in the database, if possible without crippling my app's performance.

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
David Gay
  • 1,094
  • 2
  • 16
  • 32
  • 1
    As far as I see this, `name` and `description` are going to be set in all sublasses anyways, so keeping them in the database does not make much sense. A search/sort option later can simply be done by mapping subtypes to list of attributes values instead. – avl Jul 12 '20 at 14:53
  • it's not clear what your intent is. When you define `attribute :name, default: 'special'`, then it can take other values by being updated in the database. When you define with a method, it's fixed and never changes for this STI class. Which do you want? It's fixed or it's changeable? If you want fixed values, then instead of sorting by name, you must sort by type, which is equivalent as there's a 1-to-1 correspondence. – Les Nightingill Jul 12 '20 at 20:43
  • @LesNightingill Fixed. But as I say in the last paragraph of the question, I'm concerned about disadvantages or serious performance hits that my happen when defining constants with a method, such as having to load the entire table into memory if I want to sort by the constant. EDIT in response to your edit: How would you sort by name by sorting by type instead? – David Gay Jul 12 '20 at 20:53
  • @DavidGay see my answer – Les Nightingill Jul 12 '20 at 21:55

2 Answers2

2

There are many benefits and few downsides to storing the default values in the database. But if it troubles you, you can have similar sorting efficiency by constructing your sort like this:

class SpecialWidget < Widget
  DefaultAttrs = {name: 'Special Widget', description: 'This is... etc'}
end

class Widget < ApplicationRecord
  def self.sort_by_name
    types = pluck(:type).uniq
    case_statements = types.map{|type| "WHEN '#{type}` THEN `#{type.constantize.const_get(:'DefaultAttrs')[:name]}'"
    case_sql = "CASE type #{case_statements.join(' ') END"
    order(case_sql)
  end
end

... not very elegant, but it does the job!

maybe better to put the constants in the database!

Les Nightingill
  • 5,662
  • 1
  • 29
  • 32
1

It depends entirely on the shape of your data and how you want to use it. You haven't provided enough contextual specifics to guarantee that my recommendation applies to your situation, but it's a recommendation that's specifically designed to work for 95+% of all situations.

Just Put the Data in the Relational Database

The database is the store for all things in your domain that is dynamic and needs to be persisted, i.e. state. It should be internally consistent, meaningfully self-descriptive, and well-structured in order to fully leverage the power of a relational db to flexibly manipulate and represent complex inter-related data.

Based on what you've said, and assuming that there are a bunch of different "widget types" implemented using Rail's STI implementation with a type column, I would model Widget and SpecialWidget in the database like this:

widgets
id | type
-------------------
 1 | 'Widget'
 2 | 'SpecialWidget'
 3 | 'Widget'
 4 | 'Widget'

widget_types
type            | name             | description
--------------------------------------------------------------
'Widget'        | 'Normal Widget'  | 'A normal widget.'
'SpecialWidget' | 'Special Widget' | 'This is an awfully important widget.'

You called these values a "constant", but are they really? In the purposes of your domain, will they never change like the value of Matth::PI never changes? Or will descriptions be changed, widgets renamed, widgets added, and widgets expired? Without knowing for sure I'm going to assume they're not actually Constant.

Having name and description as methods is effectively storing that widget_types table in your application source code, moving data out of your database. If you really can't afford the extra millisecond a simple JOIN for two small strings on each Widget incurs, then just load the full widget_types table into cache once on application startup, and it'll perform the same as saving it in source code.

This schema is more normalized (incurring benefits), the data itself describes all I need to know, and as you've pointed out, I can flexibly operate on that data (important since you "will definitely have to sort"). The data in this form is also extensible for future changes as they come.

Again: the database stores structured data for the purpose of on-demand flexible manipulation -- you can make up queries on the fly, and the DB can answer it.

I Really Don't Want to Put Data in the Database

Okay... then you'll have to pass that data into the database every time you want to operate on it. You can do it like so:

SELECT w.id, w.type, wt.name
FROM widgets w
INNER JOIN (
  VALUES ('Widget', 'Normal Widget'), ('SpecialWidget', 'Special Widget')
) wt(type, name) ON wt.type = w.type
ORDER BY wt.name

The VALUES expression creates an ad-hoc table mapping the class to the name. By passing in that mapping and joining on it (every time), you can tell the DB to ORDER BY it.

Kache
  • 15,647
  • 12
  • 51
  • 79
  • My values are actual constants, they will never change. Not sure why you would assume I didn't mean "constant". (Maybe you thought I was misusing the term.) And like I said, I'm using STI (because each widget has the same fields but different methods and functionality). So I don't think having a `widget_types` table would really add anything; I need to use STI anyway. The way that I described the situation is the way it really is. ;) Constants and STI. Not sure if this changes your response at all. Either way, the detailed reply is appreciated! – David Gay Jul 12 '20 at 21:54
  • 1
    It does "add something" because if you don't store the data in the database, then the db can't manipulate it for you to sort. However, if sorting is all you need to do, then you could sort on the `type` column instead (just map it first), since there's a 1:1 mapping from `type` to `name`. – Kache Jul 12 '20 at 22:04
  • 1
    @Kache I also thought that you could just sort by `type`, but the order might be different, e.g. if the name of `FrabjousWidget` is "Alice's Widget". But I think your solution is good. It may be possible to define ActiveRecord associations on Widget: `belongs_to :widget_type, foreign_key: :type` then a sort is very efficient. – Les Nightingill Jul 12 '20 at 22:22
  • 1
    @LesNightingill yeah it would be very fast -- you'd have to adversarially contrive a situation where that would become a performance concern. @DavidGay I've added another example where if you _really_ want to avoid saving the `name` in the db, you can pass in the mapping from `type` to `name` every single time to `JOIN` and `ORDER BY` when you want. – Kache Jul 12 '20 at 22:27
  • 1
    I have often encountered an aversion to putting constants in the database. Remember, though, that most of the Postgres (or mySQL) configuration is stored in the database, and it never, ever, changes. – Les Nightingill Jul 12 '20 at 22:31
  • And just as a _reductio ad absurdum_, I could say the name "John" is _constant_ in that people change names, but names themselves don't change, so we should store a `1` to represent the name in the db. – Kache Jul 12 '20 at 22:43
  • 1
    @Kache haha yes, I can't remember the attribution, but I once heard "Normalize it until it hurts and then de-normalize it until it works" – Les Nightingill Jul 13 '20 at 01:26
  • @Kache, as far as "adding something", I just meant that the type would already be in the DB from the STI type column. But I know what you mean. – David Gay Jul 14 '20 at 00:17
  • I'm accepting this answer since it's quite detailed and the comments are particularly useful, but I recommend that future readers check out the other answer by Les as well, since it too has an interesting and useful solution! – David Gay Jul 14 '20 at 00:17