December 10, 2011 at 3:54 pm
Hello
Is a good practice to put a state field in a table?, so if it is 1 means active, 0 is deleted, so i can recover the record if it is necesary?
Thank you in advance
Luis Martin
December 10, 2011 at 4:42 pm
luis martin ortiz barrutia (12/10/2011)
HelloIs a good practice to put a state field in a table?, so if it is 1 means active, 0 is deleted, so i can recover the record if it is necesary?
Thank you in advance
Luis Martin
No, in general it is not recognized as good practice. Personally I'm not a fan of logical deletes and I would stay away of them particularly in OLTP sytem.
On the other hand, if for some reason the idea is to keep a history of the values of a particular tuple you can implement something on the lines of Type 2 SCD - this implies to add three columns to your target table: DateValidSince, DateValidUntil, Status (yes, your status column).
_____________________________________
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.December 11, 2011 at 4:30 am
PaulB-TheOneAndOnly (12/10/2011)
luis martin ortiz barrutia (12/10/2011)
HelloIs a good practice to put a state field in a table?, so if it is 1 means active, 0 is deleted, so i can recover the record if it is necesary?
Thank you in advance
Luis Martin
No, in general it is not recognized as good practice. Personally I'm not a fan of logical deletes and I would stay away of them particularly in OLTP sytem.
On the other hand, if for some reason the idea is to keep a history of the values of a particular tuple you can implement something on the lines of Type 2 SCD - this implies to add three columns to your target table: DateValidSince, DateValidUntil, Status (yes, your status column).
In addition to what Paul just said, you should consider a scenario where user enters the same row again or expect the deleted row back. Usually logically deletes are done to satisfy such requirements.
But you will mess-up your tables very easily if you have constraints on these tables. For End Users it’s just logical record but in databases its physical record and should follow constraints (uniqueness, foreign key references etc).
December 11, 2011 at 6:29 am
just my 2ct to add ...
it also depends if you have more than just NULL/0/1 ( e.g. you want to know if the data "is being prepared" or "is being depricated" or "is awaiting approval xyz" ) some actually build lookup tables to document the status codes and store the status code key value in the data table because on their screens the just match the dropdown box key values.
If then again you need history of who changed what status code value and when, you could implement triggers to store that data in a topic_status_code_history object.
it all depends on the needs of your data system.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 11, 2011 at 7:59 am
PaulB-TheOneAndOnly (12/10/2011)
luis martin ortiz barrutia (12/10/2011)
HelloIs a good practice to put a state field in a table?, so if it is 1 means active, 0 is deleted, so i can recover the record if it is necesary?
Thank you in advance
Luis Martin
No, in general it is not recognized as good practice. Personally I'm not a fan of logical deletes and I would stay away of them particularly in OLTP sytem.
On the other hand, if for some reason the idea is to keep a history of the values of a particular tuple you can implement something on the lines of Type 2 SCD - this implies to add three columns to your target table: DateValidSince, DateValidUntil, Status (yes, your status column).
If the Status column is a persisted calculated column, then I'd agree 100%. If not, just leave it out.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2011 at 9:30 am
Having worked on a couple systems that have the 'IsDeleted' column, I am really not a fan. It plays hell with your indexing (every single index will likely need that column in it) and it makes queries that bit more complex. Plus there's the fun with unique constraints that it will cause (imagine a customer table with a unique constraint on email address. Now imagine the customer is deleted and reinserted)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2011 at 2:07 pm
I wonder why other DBMS like clipper , foxpro, vfp, dbase, has the option to delete records as logically delete and kepp it in the case of foxpro and vfp for at least near to 20 years
December 12, 2011 at 2:17 pm
I have preference for keep deleted rows in a history table.(Triger on DELETED copy the rows) could be nice for "Audit Reason" I think.
December 12, 2011 at 2:20 pm
GilaMonster (12/11/2011)
It plays hell with your indexing (every single index will likely need that column in it) and it makes queries that bit more complex.
If your queries typically access data which is not "deleted", this would make a good candidate for filtered indexes. There is no requirement for columns in the filter to be an index or included column, though there is a known bug if you have a not null filter in the index.
Apart from this, I surely agree that a "deleted" column can gvie a lot of funny (or not so funny) situations.
December 12, 2011 at 2:39 pm
The Active - ActiveFrom - ActiveUntil construct makes sense if the table is meant to maintain history; for example, Member and Subscriptions. You don't delete a subscription just because it expired - you want to know what offer it was tied to, etc. At the same time, you cannot simply calculate Active from the ActiveUntil because subscription can be cancelled, for example, is the member violated terms of service and subscription is, in this case, non-refundable.
So, as usual, it depends. 😉
December 12, 2011 at 2:40 pm
It's one of those things where it looks really good on paper, but usually fails miserably in actual use.
Company I used to work for used virtual deletes and virtual updates. Had a bit column in every table "Current", which was supposed to be 1 for the current version, and 0 for any and all prior versions of the data. Then a "pseudo-primary key" that worked like an ID value, tying together multiple virtual records. They also had an identity column in each table, and "CreateDate", "UpdateDate", "CreateBy" (user account ID), and "UpdateBy", on every table.
Altogether, should have made for a decent audit log, in theory.
In actuality, they didn't know it, but they had thousands of corrupted records where they had the same "pseudo-ID" and multiple "current" records, up to several thousand for one ID in at least one case, and hundreds in other cases.
Dozens of critical statistics that management relied on for daily and long-term planning were off by wide margins, because the reports were reading all those false-current values. Revenue had been misreported for tax purposes, and other minor details like that. They'd been operating that way for years and had never been able to figure out why there were constantly having trouble with reports not matching reality. The prior DBA and all of their devs and such were all convinced it was impossible for duplicate records to exist, but none of them had ever bothered to look.
They were very, very proud of the fact that none of their T-SQL code ever used the "delete" command, and only used "update" to set the Current value to 0 after inserting a new row.
So, if you decide to use virtual deletes/updates, be very, very, very sure that you can't get into that kind of situation. It's a major pain to fix, and it's not the kind of thing you want to be in the CEO's office explaining to him about how every report he's gotten from the databases for several years has been just plain garbage.
(Shortly after I discovered this problem and reported it, the manager in charge of making the original design decision on this decided that I wasn't a team player and my contract was terminated. So take my story on this with whatever grain of salt you like. I might be slightly biased. I don't think I am, but our own biases are one of our best blind spots.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 2:43 pm
luis martin ortiz barrutia (12/12/2011)
I wonder why other DBMS like clipper , foxpro, vfp, dbase, has the option to delete records as logically delete and kepp it in the case of foxpro and vfp for at least near to 20 years
foxpro and vfp??? Is that like calling C++ and VCC different languages??? 😛 FoxPro and Clipper were both nothing more than a compiler for DBASE. There are a number of reasons that DBASE is no longer popular.
I have also worked on a system with logical deletes and it is a PITA!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2011 at 10:10 pm
luis martin ortiz barrutia (12/12/2011)
I wonder why other DBMS like clipper , foxpro, vfp, dbase, has the option to delete records as logically delete and kepp it in the case of foxpro and vfp for at least near to 20 years
Because they didn't know how to delete the rows without messing up the underlying files. They had a special job to go through later to actually delete the rows. DBase II and DBase III worked the same way. It simply took them too long to delete rows. It was easier for them to mark them as deleted.
Shifting gears, I almost never delete from a table. I'll create a TYPE 2 slowly changing dimension or I'll create an archive table, but I almost never do an actual delete.
The reason why a "state" column of "IsActive" sucks so bad is because 1) it has very low uniqueness and is a terrible index candidate and 2) if give you no idea when the row became inactive. If you want a "state" column, do the right thing and create 2 columns... DateActive and DateInactive. If you read up on Type 1 slowly changing dimensions, you migh be able to get away with just one column but make it a datetime column.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply