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

i have following tables (showing relevant fields):

lots   history_id  histories   initial_date   updated_date   r_doc_date   l_doc_date   datasheet_finalized_date  users   username 

so rebuilding exisiting application dealt rather large amount of bureaucracy, , needs keep track of 5 separate dates (as shown in histories table). problem having don't know how best model in activerecord, historically it's been done having histories tables represented so:

histories   initial_date   updated_date   r_doc_date   l_doc_date   datasheet_finalized_date   username 

where 1 of 5 date fields ever filled @ 1 time...which in opinion terrible way go modeling this...

so want build unique queryable connection between every date in histories table , specific relevant user. possible use every timestamp in histories table foreign key query specific user?

i think there's simpler approach you're trying accomplish. sounds want able query each lot , find 'relevant user' (i guessing refers user did whatever action necessary update specific column on histories table). first create join table between users , histories, called user_histories:

user_histories   user_id   history_id 

i create row on table time lot's history updated , 1 of relevant dates changes. brings issue of being able differentiate specific date-type user changed (since there five). instead of using each 1 foreign key (since wouldn't unique) recommend creating 'history_code' on user_histories table represent each 1 of history date-types (much how polymorphic_type used). resulting in user_histories table looking this:

user_histories   user_id   history_id   history_code 

and example record looking this:

userhistory.sample = {   user_id: 1,   history_id: 1,   history_code: "initial" } 

allowing query specific user changed record in histories table following:

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

i recommend building these longer queries out model methods, allowing faster, cleaner query down line, example:

#app/models/history.rb  def initial_user   self.user_histories.select { |uhist| hist.history_code == "initial" } end 

this should give results want, should around whole issue of dates not being suitable foreign keys, since can't guarantee uniqueness.