CODEWITHSUNDEEP
database-designdata-bindingeditdatabase-schema
Chibuzo
Chibuzo

Reputation: 6117

What is a good way to edit a record in a database and preserve the former record

I'm developing an application that doesn't allow delete. It allows edit, but the old records must be preserved. I'm not sure the best method to implement this, but here are my thoughts.

I intend to implement this by creating a similar table to the one that contains the original record. Example tableA contains the original record, then tableA1 contains the edited version or vice versa. When the need for edit arises, I can move the edited record from tableA to the second table tableA1 and add a link that can retrieve the original record. Is this a good method, or is there a better method?

Upvotes: 1

Views: 100

Answers (3)

acutesoftware
acutesoftware

Reputation: 1091

You could use the data warehouse technique of slowly changing dimensions, where all your data is kept in the same table.

Basically you have your normal columns and a VERSION, KEY, CURRENT and REC_STATUS column. When the user edits a record you make a copy of the record back into the table, increment the VERSION and set the original REC_STATUS to 'Deleted' and the newly inserted record to 'UPDATE' or 'INSERT'

The CURRENT column is set to N for all records except the latest version, which allows for simple SELECTS, and you also have the option to look at the history. (There are other columns like REC_START_DATE and REC_END_DATE as well). See http://en.wikipedia.org/wiki/Slowly_changing_dimension

Upvotes: 1

Jean-Bernard Pellerin
Jean-Bernard Pellerin

Reputation: 12670

table myText
id  text  date  author  extraInfo

table oldText
id  text  date

oldText.id being a foreign key referencing myText.id

Then create a trigger on update to myValues which will insert into myOldValues.

It's possible this isn't normalized or the best design. An alternative would be to have no "text/date" info in myText and always use a join with myOldText. Then you wouldn't need a trigger, you'd just search for the newest entry in oldText (which you'd rename btw) with corresponding id

Upvotes: 1

Dan Drews
Dan Drews

Reputation: 1976

One method I can think of is to have a lookup table and a data table

**DataTable**
-DataID
-ColumnA
-ColumnB
-ColumnC
-...

**LookupTable**
-RecordID (This is the important ID for you)
-DataID (Key From DataTable)
-Modified DateTime (or could be revisionID or something like that to allow for order of change)

I think it's pretty self-explanatory from here, to get a new record, you select the most recenlty modfied date (or highest revision number) of the record ID you want, and join it with the dataTable

Upvotes: 1

Related Questions