Constructing a 1-many relationship with custom string foreign keys in PGSQL ActiveRecord

Question

I have the following tables (Showing only the relevant fields):

lots
  history_id

histories
  initial_date
  updated_date
  r_doc_date
  l_doc_date
  datasheet_finalized_date

users
  username

So I am rebuilding an exisiting application that dealt with a rather large amount of bureaucracy, and needs to keep track of five separate dates (as shown in the histories table). The problem that I am having is that I don't know how best to model this in ActiveRecord, historically it's been done by having the histories tables represented as so:

histories
  initial_date
  updated_date
  r_doc_date
  l_doc_date
  datasheet_finalized_date
  username

Where only one of the five date fields could ever be filled at one time...which in my opinion is a terrible way to go about modeling this...

So basically I want to build a unique queryable connection between every date in the histories table and its specific relevant user. Is it possible to use every timestamp in the histories table as a foreign key to query the specific user?

Answer 1

I think that there's a simpler approach to what you're trying to accomplish. It sounds like you want to be able to query each lot and find the 'relevant user' (I am guessing that this refers to the user who did whatever action is necessary to update the specific column on the histories table). To do this I would first create a join table between users and histories, called user_histories:

user_histories
  user_id
  history_id

I would create a row on this table any time a lot's history is updated and one of the relevant dates changes. But that now brings up the issue of being able to differentiate which specific date-type the user actually changed (since there are five). Instead of using each one as a foreign key (since they wouldn't necessarily be unique) I would recommend creating a 'history_code' on the user_histories table to represent each one of the history date-types (much like how a polymorphic_type is used). Resulting in the user_histories table looking like this:

user_histories
  user_id
  history_id
  history_code

And an example record looking like this:

UserHistory.sample = {
  user_id: 1,
  history_id: 1,
  history_code: "Initial"
}

Allowing you to query the specific user who changed a record in the histories table with the following:

history.user_histories.select { |uhist| hist.history_code == "Initial" }

I would recommend building these longer queries out into model methods, allowing for a faster, cleaner query down the line, for example:

#app/models/history.rb

def initial_user
  self.user_histories.select { |uhist| hist.history_code == "Initial" }
end

This should give you the results you want, but should get around the whole issue of the dates not being suitable for foreign keys, since you can't guarantee their uniqueness.