Rails: Is Better many small tables or one big table?

Question

In terms of performance, is it more efficient to use one large table as opposed to several smaller tables?

@animals = Animal.where(user_id: 1)

OR

@animals = Cat.where(user_id: 1) + Dog.where(user_id: 1)

Thank you very much.

In general, one large table is better than many small tables: https://stackoverflow.com/questions/21273672/many-tables-or-rows-which-one-is-more-efficient-in-sql. — Gordon Linoff, Oct 28 '17 at 02:11

score 2 · Accepted Answer · answered Oct 28 '17 at 03:30

In this particular case you are questioning about (which can be applied to similar data modeling), one table would be better. The way I'd design the model Animal is for it to have an attribute species for example.

Getting all dogs and cats would simply be:

Animal.where("species = 'dog' OR species = 'cat'")

Or you can use the Rails 5 or method like so

Animal.where(species: 'dog').or(Animal.where(species: 'cat'))

Take a look at Active Record query interface

Rails: Is Better many small tables or one big table?

1 Answers1