Sfrgttk

How to make Illustrator type tool selection automatically adapt with text length

How did passengers keep warm on sail ships?

How to substitute curly brackets with round brackets in a grid of list

Why are Marketing Cloud timestamps not stored in the same timezone as Sales Cloud?

Circular reasoning in L'Hopital's rule

Can the Right Ascension and Argument of Perigee of a spacecraft's orbit keep varying by themselves with time?

how can a perfect fourth interval be considered either consonant or dissonant?

Can a flute soloist sit?

What happens to a Warlock's expended Spell Slots when they gain a Level?

Deal with toxic manager when you can't quit

Can we generate random numbers using irrational numbers like π and e?

Why doesn't shell automatically fix "useless use of cat"?

Word for: a synonym with a positive connotation?

Student Loan from years ago pops up and is taking my salary

TDS update packages don't remove unneeded items

How do you keep chess fun when your opponent constantly beats you?

Button changing its text & action. Good or terrible?

Match Roman Numerals

Why doesn't a hydraulic lever violate conservation of energy?

Was credit for the black hole image misappropriated?

Accepted by European university, rejected by all American ones I applied to? Possible reasons?

Word to describe a time interval

What do I do when my TA workload is more than expected?

Why can't devices on different VLANs, but on the same subnet, communicate?

Versioning rows in a table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords.

Basically, historical row values need to be preserved as 'obsolete', but remain referencable as FKs from older records. These types of modifications are infrequent. I'm using MySQL, which limits things a bit. The four approaches I see are:

Naive

• PK is an auto-generated ID


• obsolete_stamp is a timestamp indicating that the row values are
  obsolete

Drawback: historical data is preserved, but there is no way to track history of changes

Better:

• PK same as above


• old_id is the id of the row that this row modifies (if exists)


• obsolete_stamp (null if row data is current)

consequence: new rows are modifications of old rows, but the new rows have to update the old_id (if there is one)

audit:

• an on update trigger creates a new row in an audit table

drawback: queries will need to check audit table for obsoleted rows

compound key:

• id + obsolete_stamp

drawback: id can't be generated anew, only if the row is 'novel' (no history); not clear how to do that automatically.

I'm one of those full-stack developers, so I know just enough about everything to be dangerous.

mysql database-design

share

edited 1 min ago

Mr.Brownstone

9,89732343

asked 9 mins ago

Jeff LoweryJeff Lowery

1011

add a comment | 

0

So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords.

Basically, historical row values need to be preserved as 'obsolete', but remain referencable as FKs from older records. These types of modifications are infrequent. I'm using MySQL, which limits things a bit. The four approaches I see are:

Naive

• PK is an auto-generated ID


• obsolete_stamp is a timestamp indicating that the row values are
  obsolete

Drawback: historical data is preserved, but there is no way to track history of changes

Better:

• PK same as above


• old_id is the id of the row that this row modifies (if exists)


• obsolete_stamp (null if row data is current)

consequence: new rows are modifications of old rows, but the new rows have to update the old_id (if there is one)

audit:

• an on update trigger creates a new row in an audit table

drawback: queries will need to check audit table for obsoleted rows

compound key:

• id + obsolete_stamp

drawback: id can't be generated anew, only if the row is 'novel' (no history); not clear how to do that automatically.

I'm one of those full-stack developers, so I know just enough about everything to be dangerous.

mysql database-design

share

edited 1 min ago

Mr.Brownstone

9,89732343

asked 9 mins ago

Jeff LoweryJeff Lowery

1011

add a comment | 

So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords.

Basically, historical row values need to be preserved as 'obsolete', but remain referencable as FKs from older records. These types of modifications are infrequent. I'm using MySQL, which limits things a bit. The four approaches I see are:

Naive

• PK is an auto-generated ID


• obsolete_stamp is a timestamp indicating that the row values are
  obsolete

Drawback: historical data is preserved, but there is no way to track history of changes

Better:

• PK same as above


• old_id is the id of the row that this row modifies (if exists)


• obsolete_stamp (null if row data is current)

consequence: new rows are modifications of old rows, but the new rows have to update the old_id (if there is one)

audit:

• an on update trigger creates a new row in an audit table

drawback: queries will need to check audit table for obsoleted rows

compound key:

• id + obsolete_stamp

drawback: id can't be generated anew, only if the row is 'novel' (no history); not clear how to do that automatically.

I'm one of those full-stack developers, so I know just enough about everything to be dangerous.

mysql database-design

share

edited 1 min ago

Mr.Brownstone

9,89732343

asked 9 mins ago

Jeff LoweryJeff Lowery

1011

share

edited 1 min ago

Mr.Brownstone

9,89732343

asked 9 mins ago

Jeff LoweryJeff Lowery

1011

share

edited 1 min ago

Mr.Brownstone

9,89732343

asked 9 mins ago

Jeff LoweryJeff Lowery

1011

edited 1 min ago

Mr.Brownstone

9,89732343

edited 1 min ago

Mr.Brownstone

9,89732343

asked 9 mins ago

Jeff LoweryJeff Lowery

1011

asked 9 mins ago

Jeff LoweryJeff Lowery

1011