title: How to maintain record history on table with one-to-many relationships?
body: <p>I have a "services" table for detailing services that we provide. Among the data that needs recording are several small one-to-many relationships (all with a foreign key constraint to the service_id) such as:</p>
  
  <pre><code>service_owners -- user_ids responsible for delivery of service
  service_tags -- e.g. IT, Records Management, Finance
  customer_categories -- ENUM value
  provider_categories -- ENUM value
  software_used -- self-explanatory
  </code></pre>
  
  <p>The problem I have is that I want to keep a history of updates to a service, for which I'm using an update trigger on the table, that performs an insert into a history table matching the original columns. However, if a normalized approach to the above data is used, with separate tables and foreign keys for each one-to-many relationship, any update on these tables will not be recognised in the history of the service.</p>
  
  <p>Does anyone have any suggestions? It seems like I need to store child keys in the service table to maintain the integrity of the service history. Is a delimited text field a valid approach here or, as I am using postgreSQL, perhaps arrays are also a valid option? These feel somewhat dirty though!</p>
  
  <p>Thanks.</p>