0

I'm using MySQL workbench's EER tool to make the database design for an application I'm developing. it is very important to track data changes on the DB, so i was following this post which makes a lot of sense the accepted answer. the problem im having is that i am not good understanding the primary keys so im a little confused on how this should work, since every time a change is made there will be another row inserted with the same Id, therefore the DB wouldn't allow it am I right? And i need to use a primary key because of foreign keys.

pato.llaguno
  • 741
  • 4
  • 20
  • The answer you reference uses a composite PK, customer id can be repeated as long as valid_from and/or valid_until are different; such a PK uses the combination all it's fields for identity. (One problem I see with that answers details is that it refers to using triggers, but triggers cannot modify the tables they are "ON"). I favor a solution involving a "history" table that is updated and populated by triggers on the "current" table. – Uueerdo Oct 20 '17 at 21:04
  • @Uueerdo ohh, i see but then its wrong to hace valid_until as primary key since that can be nullright?Another thing i am observing is that EER is making primary key the columns to reference the foreign keys, is this the best of practices? – pato.llaguno Oct 20 '17 at 21:08
  • NULL values can be a part of primary keys and unique indexes; but behave a little differently (Any two NULLs are "unique" from each other.) That answer could have gotten away with just an (id, from) PK; (id, from, to) doesn't prevent data like (0,1,4) and (0,2,5) existing at the same time causing issues when you are looking for "when 3 between from and to". – Uueerdo Oct 20 '17 at 21:12
  • @Uueerdo "PRIMARY KEY - A unique index where all key columns must be defined as NOT NULL." https://dev.mysql.com/doc/refman/5.7/en/create-table.html – Paul Spiegel Oct 20 '17 at 21:23
  • Ah, yeah, good catch there. I forgot about that difference between primary and unique keys; since I avoid nulls in unique keys unless unusually appropriate, I've come to lump them together. – Uueerdo Oct 20 '17 at 21:36
  • if that is the case, there is no problem on only using (id,valid_from) as pk, that's enough from what i understand, on the other hand, what is the correct practice when making an edit/delete? in the case of insert i just have to insert a new row, but on the other one i have to make an update and insert, should i do this on a transaction? or one after the other? what are the best practices? even maybe stored procedure? – pato.llaguno Oct 20 '17 at 21:36

1 Answers1

0

My normal "pattern" in (very)pseudo code:

  • Table A: a_id (PK), a_stuff
  • Table A_history: a_history_id (PK), a_id(FK referencing A.a_id), valid_from, valid_to, a_stuff

Triggers on A:

  • On insert: insert values into A_history with valid_from = now, and valid_to = null.
  • On update: set valid_to = now for last history record of a_id; and do the same insert from the "on insert" trigger with the updated values for the row.
  • On delete: set valid_to = now for last history record of a_id.

In this scenario, you'd query history with "x >= from and x < to" (not BETWEEN as the a previous record's "from" value should match the next's to "value").

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • a_id reflects the last state or the starting state? – pato.llaguno Oct 20 '17 at 21:46
  • a_id references the single row in table A; it shows that the row in table A_history is a state for that row in A (a previous state if valid_to is not null); table A will only ever have the current state. – Uueerdo Oct 20 '17 at 21:48
  • thanks, i see why it might be better to have a separate table, might help performance on queries right? – pato.llaguno Oct 20 '17 at 21:52
  • How much it helps performance depends on usage. It mainly standardizes tracking of changes to data. If you provide facilities in the system it supports to "go back to yesterday and fix something that should have been done" you can end up working out of the history table more than the "current" table; and the current table just becomes used for your configuration management. But if you only allow "current" actions, those actions never have to worry about filtering out old data. – Uueerdo Oct 20 '17 at 21:54