Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2005-12-06 : 17:19:42
|
| HelloWe have a large sql db, reasonably normalized. Much of our data is hooked to "point in time" type of analysis.Where literally we need to keep data, but it needs to be deleted (logically or physically). If we do a logical delete, we can maintain data integrity, but the data design is very relational so setting up one table to handle a logical delete results in many database objects needing the additional where criteria. As anyone ever come across a grand way of handling logical deletes of data, (because old records point to these "need to be deleted" records. Confusing as all getout. I thought I came across a long document on designing data to work in the time sensitive model. Any thoughts.________________________________________________Drinking German Beer... fun.Listening to an accordian player play ACDC...priceless |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-06 : 17:33:49
|
| I have a column in my tables like this:DELETED BIT NOT NULL DEFAULT 0; -- Set to 1 if deleted |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-12-06 : 19:54:12
|
| I do something like Sam does as well, along with a CreDate and CngDate (Creation Date and Changed Date). SO I can quickly see when a record was created, and when it was last changed.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-06 : 20:00:39
|
| How about a StillReferenced column too?Set the whole lot to zero (on all tables, only where currently 1 will do I reckon)Then for all records that have the DELETED bit column = 1 set the StillReferenced = 1 where any associated records can be found (that are not themselves marked as DELETED).Then delete all the rows that are marked as DELETED and the StillReferenced column = 0Kristen |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-06 : 20:19:14
|
quote:
Originally posted by MichaelP
I do something like Sam does as well, along with a CreDate and CngDate (Creation Date and Changed Date). SO I can quickly see when a record was created, and when it was last changed.
Do you maintain CngDate with a Trigger? |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-12-06 : 20:59:08
|
quote:
Originally posted by Kristen
How about a StillReferenced column too?Set the whole lot to zero (on all tables, only where currently 1 will do I reckon)Then for all records that have the DELETED bit column = 1 set the StillReferenced = 1 where any associated records can be found (that are not themselves marked as DELETED).Then delete all the rows that are marked as DELETED and the StillReferenced column = 0Kristen
Interesting.... We usually have the createID, EditID, CreateDate, EditDate and then a status column or "deleted" bit. The application maintains those fields (used effectively for are auditing tables with triggers). One thing that was brought up was using an incremental small int field to make each table have a PK that makes up a ID column and the smallINT. so that each "archived" version of the record, takes an increment and the most current would always be 1.________________________________________________Drinking German Beer... fun.Listening to an accordian player play ACDC...priceless |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-12-07 : 19:07:50
|
| Sam, we used to, but we got away from it due to performance / complexity. It's just a coding rule that we impliment in our update statements now. So, it's not 100% sneaky dba proof, but it's good enough for what we need it for in the particular database it's used in.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
|
|
|
|
|
the logical delete