Expire rather than Delete records in a table

Question

Expire rather than Delete records in a table

timcarper

Old Hand

Points: 344
More actions
July 16, 2010 at 1:16 pm

#226958

Warning - This could potentially become a religion debate very quickly. I am specifically interested in Microsoft SQL Server best practices and also your real-life experiences with this situation.
The setup: I am creating a database that stores contact information in 3 tables (Address, Telephone, Email) and uses a Contact superclass table for the PK/FK INTs on these 3 tables. Also included in this DB are two spots (Outgoing Alert Notifications, Inbound Communications) that reference these Contact tables.
The problem: For Referential Integrity to be maintained, I am not allowing records to be deleted. Also I am not allowing the important values of these 3 Contact tables to be modified if they have ever been used before (in the Outgoing or Inbound tables). Thus, there becomes a need to be able to "Expire" a given Contact record for the case that this record becomes no longer valid.
The question: Is it better to go ahead and "Future Expire" these records on insert and default the column to something outrageously in the future like '12/31/2100', or is it better to leave the Expiration field null?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

timcarper (7/16/2010)
Warning - This could potentially become a religion debate very quickly. I am specifically interested in Microsoft SQL Server best practices and also your real-life experiences with this situation.
The setup: I am creating a database that stores contact information in 3 tables (Address, Telephone, Email) and uses a Contact superclass table for the PK/FK INTs on these 3 tables. Also included in this DB are two spots (Outgoing Alert Notifications, Inbound Communications) that reference these Contact tables.
The problem: For Referential Integrity to be maintained, I am not allowing records to be deleted. Also I am not allowing the important values of these 3 Contact tables to be modified if they have ever been used before (in the Outgoing or Inbound tables). Thus, there becomes a need to be able to "Expire" a given Contact record for the case that this record becomes no longer valid.
The question: Is it better to go ahead and "Future Expire" these records on insert and default the column to something outrageously in the future like '12/31/2100', or is it better to leave the Expiration field null?

Okay. I'll behave therefore I'll refrain from telling you how against "soft deletes" I am 😀

Answering your specific question, what's wrong with initially populating expiration column to null? As per quantum theory until you look at the row the row will be both expired and non-expired meaning "expiration" is an not-known value which is very well represented by Null 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

When I need to use the "future expire" method, I use 9999-12-31 for the future date. As a side bar, I prefer the "future expire" method when start and end dates are involved. For me, it makes coding a bit simpler.

I'm neither for nor against "soft deletes"... it depends on what is necessary to protect the data for a given installation. I will say the "soft deletes" in the form of Slowly Changing Dimensions - Type II are one of my favorites for "soft deletes".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Here's a pretty good link that explains SCD's quite nicely....

http://en.wikipedia.org/wiki/Slowly_changing_dimension

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

I tend to agree with what's listed above, but I'd be careful about not allowing reuse and using things like phone or email as PK values.

I have had re-used emails, and I suspect that sjones at peoplesoft goes to someone else.

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 5

I would consider adding a column that, in effct, marks the record for deletion. It could be as simple as a BIT column. Foxprow's used that before (although the column was hidden.)

p.s., but be aware that if you do this, it means that almost every time you query that table you will have to tack on "AND deletedCol <> 1" in the WHERE clause. It gets old after a while.

Also, I would recommend using one column for modify and/or create date and one that simply marks it for deletion too.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

How about WHERE EndDate <= GETDATE(). That way you can "future date" expiration dates and never have to worry about them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

Jeff Moden (7/16/2010)
When I need to use the "future expire" method, I use 9999-12-31 for the future date. As a side bar, I prefer the "future expire" method when start and end dates are involved. For me, it makes coding a bit simpler.
I'm neither for nor against "soft deletes"... it depends on what is necessary to protect the data for a given installation. I will say the "soft deletes" in the form of Slowly Changing Dimensions - Type II are one of my favorites for "soft deletes".

Jeff is correct. I do agree that in a data warehouse environment Type II SCDs should include a set of valid_from/valid_until columns.

Having said that, I do not think of non-current Type II SCD rows as "deleted" rows but as rows that describe whatever-they-describe at some specific point in time.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

timcarper Old Hand Points: 344 More actions · Answer 8

I disagree with the idea of using a bit flag to determine deleted records. Really a better idea for this scenario to know WHEN this record became no longer applicable.

That said, the only question I had was whether to set a default for the End Date that's so far out in the future it'll never be accidentally traversed or if it should be left null. DBProj seems to really dislike nulls.