Rails 4 - Time Complexity: better to store a t.reference or to store only relevant information in the migration?

Question

Currently I have a model that details a relationship between two people.

I.e.:

Relationship
t.references :person_one
t.references :person_two

When one of these people view the Relationship, they see the other person's name. Does it make more sense from a time-standpoint to store just the references? Or does it make more sense to do something like this:

Relationship
t.string :person_one
t.string :person_two

Each person is also able to view all the relationships which they're part of. I.e., every person has_many relationships.

It seems that t.references is just a way to abstract adding a person_id column, but it returns the desired Person object, rather than the id. I can definitely see the pros and cons of each. In the former, it seems like it may be easier (code-wise) to get all of a single person's relationships using person.relationships, but it also seems that a lot of behind-the-scenes querying seems to be happening under that implementation, and what I'm interested in is optimizing for time complexity/reducing the number of times I have to go through my SQL databases. But I may be misunderstanding the precise way in which Rails stores and then retrieves this data.

Thoughts?

Answer 1

More than likely your "extra querying" is due to N+1 queries required for loading your relationships. You should research ActiveRecord#includes to get a better understanding of how to keep your queries tight. When you use #includes Rails will optimize the queries to make the fewest queries, but it will still require some guidance on your part.

Given the following models:

class Person < ActiveRecord::Base
  has_many :relationships
  has_many :relatives, through: :relationships, class_name: "Person", foreign_key: "relative_id"
end

class Relationship < ActiveRecord::Base
  belongs_to :person
  belongs_to :relative, :class_name => "Person", :foreign_key => 'relative_id'
end

When you try to query a person's relatives you want to ensure that you include the relationships and the person.

Person.where(name: 'George').includes(:relationships => :relative)

This will cause Rails to make three queries. The first will query Person with the name of George, the second will be all Relationships with a person_id that matches the id from the first result, and the third will be all Persons with an id that matches the relative_id from the second query.

Answer 2

If you build your application using best practices when it comes to normalisation of the database and appropriate use of eager loading, then the performance of the database will be of no concern to you.

Do not be misled into introducing redundant data, with person names being used in multiple tables to avoid using joins -- your data integrity problems and the efforts you will go through to (unsuccessfully) keep the data in synch will will be prohibitive, and wipe out any savings you might think you accrue through easier coding.