October 23, 2015 at 9:08 am
Eric M Russell (10/23/2015)
To determine active / inactive status, I'm not looking for a specific value; it'scontextual based on a range comparison.
-- return all currently active customers:
select * from Customers where InactiveDate > getdate();
-- return all customers active as of Juy 15, 2015:
select * from Customers where InactiveDate > '2015-07-15';
An interesting thought. I got as far as:
-- return all currently active customers:
select * from Customers where InactiveDate is null;
...but your second example has made me think. My suggestion would give:
-- return all customers active as of July 15, 2015:
select * from Customers where InactiveDate is null or InactiveDate > '2015-07-15';
Which is less efficient. Looks like that's my 'learn something every day' moment for today :hehe:
October 23, 2015 at 9:09 am
Being from the "old" school and having been taught assembler, cobol and RPG back in the early 70's, I learned then to avoid negative logic, so I would think I'm a positive type. However, in recent years moving over to Access, VB, .Net SQL 7 and better, more and more I have had to be flexible in my thinking. Personally, I like the column naming convention of "*_flag" appended to the name of any column used to verify 'status' of a row of data. Having a date value associated with the 'status' column allows processing to 'archive' older non-active status data, or delete such rows if a retention period has been reached, using positive logic, of course!! Lately, retention periods for records have raised interesting processing issues in our organization (government) just to reduce non-critical data being retained in the 'active' database.
October 23, 2015 at 9:16 am
I very much prefer using IsActive, or simply put Active. I am adamant about this because at my previous job the guy who set up the database was completely inconsistent. Sometimes he'd use a column named Active in a table. And then another table he'd use InActive. There was no way of figuring out his reasoning; why he'd use one at one time and then use another a different time. I liked a lot of what he did, but not this. I vowed, whenever I design a database, to always use columns named Active (or I guess I could use IsActive) if I needed a column to notify me as to whether or not a record should be included in a currently active listing, or whatever.
However if people prefer to use InActive, I'm OK with that. But just please, for the love of God, be consistent!!!
Kindest Regards, Rod Connect with me on LinkedIn.
October 23, 2015 at 9:47 am
Of course we are assuming that the use of a flag is the appropriate solution?
If a flag is for information then I'd use the positive terminology.
If the flag is for selecting data then I'd take a look at the data model and also the data to see what the cardinality would be. Maybe a flag is not the appropriate way to do this. A table with the active PK values might be the appropriate way to handle this.
October 23, 2015 at 9:47 am
Rod at work (10/23/2015)
I very much prefer using IsActive, or simply put Active. I am adamant about this because at my previous job the guy who set up the database was completely inconsistent. Sometimes he'd use a column named Active in a table. And then another table he'd use InActive. There was no way of figuring out his reasoning; why he'd use one at one time and then use another a different time. I liked a lot of what he did, but not this. I vowed, whenever I design a database, to always use columns named Active (or I guess I could use IsActive) if I needed a column to notify me as to whether or not a record should be included in a currently active listing, or whatever.However if people prefer to use InActive, I'm OK with that. But just please, for the love of God, be consistent!!!
Sometimes it's useful to have a logical abstraction layer that hides the inconsistent details of naming conventions or functional implementation. For example, create a view called vActiveCustomers around the table Customers. Exactly what filtering or column aliasing is done within the view depends who originally developed the table... or what "standard" the developer chose to apply when she created the table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 23, 2015 at 9:56 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.
I guess this makes sense. I try to avoid denormalization unless performance reasons dictate otherwise (very rare). But in this case I'll make an exception. At least for now until I think about this more.
October 23, 2015 at 9:59 am
Positive only, for readability. Is means the column is a bit.
October 23, 2015 at 10:08 am
GeorgeCopeland (10/23/2015) Is means the column is a bit.
George, thank you for this definition of IS. Certain politicians have been arguing over this for more than 10 years. Thanks. 😀
October 23, 2015 at 10:16 am
Keep things simple, use Active or IsActive, and not InActive UNLESS there is a small subset of records with that value which helps a crucial filtered index...
Hakim Ali
www.sqlzen.com
October 23, 2015 at 10:55 am
I first learned database design in the late 70's, and it was pretty much "IsActive" everywhere.
you would rarely see a IsInactive. I think it was the oddball guy who used IsInactive.
Dennis Parks
MCSE, MCDBA, MCSD, MCAD, MCTS
October 23, 2015 at 11:24 am
We use both IsValid (default is 0) and IsDeleted (for soft deletes).
October 23, 2015 at 11:49 am
I prefer to use [IsntInactive] where a code of 100 - 255 indicates various reasons for it's non inactivity, codes <= 99 indicate inactivity, and 0 means not specified.
😉
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 23, 2015 at 12:20 pm
nelsonj-902869 (10/23/2015)
GeorgeCopeland (10/23/2015) Is means the column is a bit.
George, thank you for this definition of IS. Certain politicians have been arguing over this for more than 10 years. Thanks. 😀
It's actually refreshing to know that I'm not the only person who remembers that infamous quote. 😛
And yes, apparently to "wipe a server" involves using a cloth. Sigh. I often wonder if politicians think people really are as stupid as they seem to think we are.
October 23, 2015 at 12:22 pm
Eric M Russell (10/23/2015)
I prefer to use [IsntInactive] where a code of 100 - 255 indicates various reasons for it's non inactivity, codes <= 99 indicate inactivity, and 0 means not specified.😉
Eric, that's pretty twisted. It just leaves one open question...Do you use those values casted from your datetime column?
Don't worry. I have no questions about negative numbers.
October 23, 2015 at 12:39 pm
[IsntInactive] = Isnt Inactive = Is NOT Inactive = Is Active? - IMHO that has to be a joke, right? Talking about confusing.
It's probably just me but the whole 'Is' part just bugs me. I would rather see a field called 'Active_Flag' or 'Inactive_Flag'.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 46 through 60 (of 129 total)
You must be logged in to reply to this topic. Login to reply