Building 1-many relationship with custom string foreign keys in PGSQL ActiveRecord
I have the following tables (only relevant fields are shown):
lots
history_id
histories
initial_date
updated_date
r_doc_date
l_doc_date
datasheet_finalized_date
users
username
So, I am rebuilding an existing application that deals with quite a lot of bureaucracy and needs to keep track of five separate dates (as shown in the story table). The problem I'm running into is that I don't know how best to model this in ActiveRecord, historically this has been done if history tables are represented like this:
histories
initial_date
updated_date
r_doc_date
l_doc_date
datasheet_finalized_date
username
If only one of the five date fields can be filled in at a time ... which in my opinion is a terrible way of simulating this ...
So, basically I want to create a unique queried join between each date in the history table and its specific corresponding user. Is it possible to use each timestamp in the history table as a foreign key to query a specific user?
I think there is a simpler approach to what you are trying to accomplish. It sounds like you want to be able to query each batch and find the "matching user" (I assume this refers to the user who did whatever was necessary to update a specific column in the history table). To do this, I would first create a table of connections between users and stories called user_histories:
user_histories
user_id
history_id
I would create a row in this table whenever the multi-page history is updated and one of the corresponding dates changes. But now the question arises about the ability to distinguish which specific date type the user actually changed (since there are five of them). Instead of using each of them as a foreign key (since they would not necessarily be unique), I would recommend creating a "history_code" in the user_histories table to represent each of the history date types (similar to using the polymorphic type). Result in the user_histories table as follows:
user_histories
user_id
history_id
history_code
And an example entry looks like this:
UserHistory.sample = {
user_id: 1,
history_id: 1,
history_code: "Initial"
}
Allows you to query a specific user who changed an entry in the history table with the following:
history.user_histories.select { |uhist| hist.history_code == "Initial" }
I would recommend building these longer queries in model methods, allowing a faster and cleaner query down the line, like so:
#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 work around the whole problem when dates are not appropriate for foreign keys, since you cannot guarantee that they are unique.
source to share