October 23, 2015 at 7:05 am
I use IsActive. I usually put a default on my bit columns and I use 0 because I explicitly want the row to be set to active. It might not always need to be that way but that's how I do it.
If there are more than 2 states, like some have mentioned in the discussion, I do not use a char column that needs to be interpreted I use a separate table to hold the states and have a name and description there and have an id that I use an FK.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 23, 2015 at 7:06 am
If I had to go with an True | False type indicator, then I'd go with a positive indicator; a tinyint, not nullable, and constrained to the values 0 or 1.
IsActive tinyint not null check (IsActive in (0,1))
However, from a design perspective, I've found a Date type is more useful for indicating active / inactive status. I want to know when, not just if, a row changed to inactive status.
InactiveDate date not null default ('9999-12-31')
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 23, 2015 at 7:11 am
Eric M Russell (10/23/2015)
I've found a Date type is more useful for indicating active / inactive status. I want to know when, not just if, a row changed to inactive status.
InactiveDate date not null default ('9999-12-31')
You wouldn't use "InactiveDate is null" as an indicator then, you'd have comparisons around your code for the 9999 date?
October 23, 2015 at 7:15 am
Most of our data is considered to be active unless otherwise noted. So we have a field called 'Deleted_Flag', this has an 'A' if it is active and a 'D' if it has been deleted. So I guess that makes us more in the negative thinking camp. 🙂
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 23, 2015 at 7:36 am
david.wright-948385 (10/23/2015)
Eric M Russell (10/23/2015)
I've found a Date type is more useful for indicating active / inactive status. I want to know when, not just if, a row changed to inactive status.
InactiveDate date not null default ('9999-12-31')You wouldn't use "InactiveDate is null" as an indicator then, you'd have comparisons around your code for the 9999 date?
To determine active / inactive status, I'm not looking for a specific value; it's contextual based on a range comparison.
-- return all currently active customers:
select * from Customers where InactiveDate > getdate();
-- return all customers active as of July 15, 2015:
select * from Customers where InactiveDate > '2015-07-15';
However, in actuality, there is usually both an ActiveDate and InactiveDate, because we don't want to return customers that were inserted after July 15, 2015. If you simply have an IsActive column on your table, the business (typically an executive from accounting) will one day start asking questions for which your database doesn't have the answer, even if the application's day to day operation doesn't really need that detail.
-- return all customers active as of July 15, 2015:
select * from Customers where '2015-07-15' between ActiveDate and InactiveDate;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 23, 2015 at 7:56 am
angel-626714 (10/23/2015)
I would also have a column for the date and time it became inactive.
Denormalisation alert
That's perfectly fine. It relates to the individual record. Where's the denormalization in that?
October 23, 2015 at 8:14 am
It is interesting how different people may choose to use an active vs inactive column on a table. In some cases it may make more sense to choose the positive or negative point of view and it is going to be important to stick to this convention which ever way you choose. Aside from clarity or person preference there may be a performance reason for this. I could see a use case where more often then not the records in a table are marked "active". We may have a need to query for inactive records only, in which case a filtered index on this column where inactive = 1 could be useful and would hold the small subset of inactive records.
October 23, 2015 at 8:15 am
RonKyle (10/23/2015)
That's perfectly fine. It relates to the individual record. Where's the denormalization in that?
You have two indicators for the same thing: the flag, and whether the date is null. So it's possible to have a non-null cancellation date with a flag that says the record is active and vice versa.
October 23, 2015 at 8:26 am
I would definitely opt for the positive way, if I had not been hunting down database defaults overwritten by ORM-defaults on insert. Great if you define an Active or IsActive bit field with a default value of 1, but if that field is not initialized before a new record is added through Entity Framework it will be set to 0 anyway (false in .NET). Thus it might depend on the initial state of such flag, knowing that developers might forget to set it properly in a new record when its value will not be included in the corresponding data entry from. Assuming a .NET style default value for non-nullable fields (zero in most cases) avoids a mismatch between object and database defaults that might confuse either side. Notice that I consider the initial state, not the common state of a field. When all records are initially active, in the end you might have far more inacitve records and even consider a filtered index for the active ones. What are your experiences with ORMs on this matter?
October 23, 2015 at 8:34 am
inactive. Generally speaking, there is no reason for a row initially existing were it not to be "active". Flagging it as inactive is a soft deletion. SELECT imho
October 23, 2015 at 8:39 am
I will go either way depending on which of the two options I believe will be the "more normal mode". If my expectation is that most items will be active then I'll use IsInactive. On the other hand, if the inactive group is expected to be the smaller set then it's definitely an IsActive flag. That way, when I'm looking down a long list of rows the "exceptions" stick out with their big old zeroes showing.
/john
October 23, 2015 at 8:51 am
david.wright-948385 (10/23/2015)
RonKyle (10/23/2015)
That's perfectly fine. It relates to the individual record. Where's the denormalization in that?You have two indicators for the same thing: the flag, and whether the date is null. So it's possible to have a non-null cancellation date with a flag that says the record is active and vice versa.
So, does anyone care about the date when something was set to inactive status.
You would assume if a record exist, it's active so no need for a date. Only set the date when it's inactive.
Sometimes you may want to know how many customers are active and how many were active at a given point in time.
How else would you set this up?
October 23, 2015 at 8:56 am
I suspect I fall more into the "negative" category.
I'd have a field "inactive" or "disabled" or the like, if the value is 1 then the record is inactive / disabled, any other value it's still a "live" record. Probably make it a bit and allow NULLs.
Probably some of my mindset on this is, I see the field as a "switch" with 1 meaning that that field is "on."
One thing I would NOT do, however, is flip flop between methods in the DB, as that would make it rather difficult to maintain and keep track of...
October 23, 2015 at 9:04 am
Definitely agree with the last sentence. I did some dev and dba work for a shop a couple of years ago, and inconsistencies like this were brutal. One contract I didn't mind seeing end!
October 23, 2015 at 9:04 am
angel-626714 (10/23/2015)
david.wright-948385 (10/23/2015)
You have two indicators for the same thing: the flag, and whether the date is null. So it's possible to have a non-null cancellation date with a flag that says the record is active and vice versa.So, does anyone care about the date when something was set to inactive status.
You would assume if a record exist, it's active so no need for a date. Only set the date when it's inactive.
Sometimes you may want to know how many customers are active and how many were active at a given point in time.
How else would you set this up?
Ditch the flag and use the fact that the "inactive date" field is null (or some other value) to show that the record is active.
Viewing 15 posts - 31 through 45 (of 129 total)
You must be logged in to reply to this topic. Login to reply