Rails Data Modelling

Question

In my company, we are trying to cache some data that we are querying from an API. We are using Rails. Two of my models are 'Query' and 'Response'. I want to create a one-to-many relationship between Query and Response, wherein, one query can have many responses.

I thought this is the right way to do it.

Query = [query]
Response = [query_id, response_detail_1, response_detail_2]

Then, in the Models, I did the following Data Associations:

class Query < ActiveRecord::Base
  has_many :response
end

class Response < ActiveRecord::Base
  belongs_to :query
end

So, canonically, whenever I want to find all the responses for a given query, I would do -

"_id" = Query.where(:query => "given query").id
Response.where(:query_id => "_id")

But my boss made me use an Array column in the Query model, remove the Data Associations between the models and put the id of each response record in that array column in the Query model. So, now the Query model looks like

Query = [query_id, [response_id_1, response_id_2, response_id_3,...]]

I just want to know what are the merits and demerits of doing it both ways and which is the right way to do it.

I don't understand your code. What is `Query = [query]`? How is `"_id" = Query.where(...)` supposed to work? — Stefan, Feb 26 '15 at 16:31
Welcome to Stack Overflow. This question is soliciting opinions, not fixes or facts, which makes it off-topic. A better way to go about getting the answer you want is to directly ask your boss why one is preferable over another; Your boss should be very open to explaining. — the Tin Man, Feb 26 '15 at 17:56

score 0 · Answer 1 · edited May 23 '17 at 12:28

If the relationship is really a one-to-many relationship, the "standard" approach is what you originally suggested, or using a junction table. You're losing out on referential integrity that you could get with a FK by using the array. Postgres almost had FK constraints on array columns, but from what I researched it looks like it's not currently in the roadmap: http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/

You might get some performance advantages out of the array approach if you consider it like a denormalization/caching assist. See this answer for some info on that, but it still recommends using a junction table: https://stackoverflow.com/a/17012344/4280232. This answer and the comments also offer some thoughts on the array performance vs the join performance: https://stackoverflow.com/a/13840557/4280232

Another advantage of using the array is that arrays will preserve order, so if order is important you could get some benefits there: https://stackoverflow.com/a/2489805/4280232

But even then, you could put the order directly on the responses table (assuming they're unique to each query) or you could put it on a join table.

So, in sum, you might get some performance advantages out of the array foreign keys, and they might help with ordering, but you won't be able to enforce FK constraints on them (as of the time of this writing). Unless there's a special situation going on here, it's probably better to stick with the "FK column on the child table" approach, as that is considerably more common.

Granted, that all applies mainly to SQL databases, which I notice now you didn't specify in your question. If you're using NoSQL there may be other conventions for this.

Rails Data Modelling

1 Answers1