October 28, 2015 at 9:05 am
Wow. This discussion is almost as lively as the whole tabs versus spaces debate.
I've used 'Active bit not null default 1' for so many years I never thought to question it.
Either way... Doesn't matter, as long as you're consistent.
Why does the phrase 'Yes, we have no bananas' come to mind when thinking of a column named 'Inactive?' And please consider that casing a column name as 'InActive' indicates 2 words, 'In' and 'Active' which is really confusing.
Mark
Just a cog in the wheel.
October 28, 2015 at 9:12 am
starunit (10/28/2015)
...And please consider that casing a column name as 'InActive' indicates 2 words, 'In' and 'Active' which is really confusing.
How did I miss that!!! I am feeling stupid right now.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 28, 2015 at 9:13 am
Gary Varga (10/28/2015)
Eric M Russell (10/28/2015)
...another advantage of Char(1) is that it offers the flexibility of supporting additional codes in the future. Computers reduce the world as 0 | 1, but SQL and relational databases are at the logical level and must answer questions like "Is this insurance policy active?", so you may need to add something like 'P' for Pending and 'S' for Suspended...Now that is a different argument to which I would say you are suggesting values of 'P' and 'S' for an IsActive column which would be wrong in my opinion. This is clearly changing the scope of the column to represent a status so then the column name needs to be changed to reflect this. Thus this no longer is an attempt to answer the question posed in the editorial in the first place.
You're right, to support anything beyond Y | N, the column would need to be something like ActiveStatus char(1). My comment about multiple value status columns, and also my earlier comment about ActiveDate paired with InActiveDate, is questioning the practical usefulness of boolean columns in relational data modeling, which is straying off topic. It's just that from my experience most line of business applications don't ask questions like IsPolicyActive, IsProspectMarried, or IsAddressDeleted; they want to know not only if, but also why, a claim against a specific policy is payable or during what time period was the customer using this specific home address. If you look at the tables I model, you'll rarely see a column containing only 0 or 1.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 28, 2015 at 9:22 am
IsActive strongly implies a Boolean, use the right datatype for the data without exception, I don't care how much storage on disk. If the datatype has multiple valid values, it is no longer a flag it is a code and should be called ActiveCode. 0 always means false, any other value means true, everybody knows this and it is not even worth discussing.
October 28, 2015 at 9:41 am
Eric M Russell (10/28/2015)
Gary Varga (10/28/2015)
Eric M Russell (10/28/2015)
...another advantage of Char(1) is that it offers the flexibility of supporting additional codes in the future. Computers reduce the world as 0 | 1, but SQL and relational databases are at the logical level and must answer questions like "Is this insurance policy active?", so you may need to add something like 'P' for Pending and 'S' for Suspended...Now that is a different argument to which I would say you are suggesting values of 'P' and 'S' for an IsActive column which would be wrong in my opinion. This is clearly changing the scope of the column to represent a status so then the column name needs to be changed to reflect this. Thus this no longer is an attempt to answer the question posed in the editorial in the first place.
You're right, to support anything beyond Y | N, the column would need to be something like ActiveStatus char(1). My comment about multiple value status columns, and also my earlier comment about ActiveDate paired with InActiveDate, is questioning the practical usefulness of boolean columns in relational data modeling, which is straying off topic. It's just that from my experience most line of business applications don't ask questions like IsPolicyActive, IsProspectMarried, or IsAddressDeleted; they want to know not only if, but also why, a claim against a specific policy is payable or during what time period was the customer using this specific home address. If you look at the tables I model, you'll rarely see a column containing only 0 or 1.
IMHO I think this has morphed from a question of "Positive or Negative" logic into more of "Should we be coding for only Positive or Negative". And what I mean is yes as of today a field may be a "Yes or No", "1 or 0", "Active or Not Active", ... But should we be more forward thinking and allow for more values? So more of a "Status_Code" type of field instead of "IsActive" or "IsNotActive". I would more lean to having a Status_Code field, just my two cents. 😎
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 28, 2015 at 9:41 am
below86 (10/28/2015)
+100 to Eric's last post.
Except for the part about having to guess whether a 1 is true or false, I'll second that.
As for the reason why some systems use -1 for true, it's because -1 sets all bits in a byte to "1" including the sign bit. 0 would of course, have all bits, including the sign bit, set to 0.
In many cases, I follow the "rule" that 0 is false and anything else can represent a true because that's the way the BIT datatype in SQL Server works. Assign any non-zero numeric value to a BIT datatype and it will return a 1 providing you don't get caught by some form of rounding. ANY non-zero numeric will cause the BIT datatype to set to 1. For example...
SELECT SomeBit = CAST(1e-30 AS BIT);
... returns ...
SomeBit
-------
1
(1 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2015 at 9:51 am
I don't use bit very often, but when I do, it's NOT NULL and has a default.
Mostly, for other, general status columns, I'll use whatever's appropriate - int, tinyint, varchar(x) - which tie to a reference code table.
But for an indicator as to whether a record should be considered when pulling data for current operational needs, a simple Active flag is all I need 95% of the time. So that's what I use. (the KISS method I suppose).
Mark
Just a cog in the wheel.
October 28, 2015 at 12:05 pm
Jeff Moden (10/28/2015)
As for the reason why some systems use -1 for true, it's because -1 sets all bits in a byte to "1" including the sign bit. 0 would of course, have all bits, including the sign bit, set to 0.
For an example of why having a true Boolean data type is important, consider VisualBasic. There is now a true boolean data type, however, you are allowed to test a numeric data type variable for "true" or "false", where "false" = 0, and "true" = any non-zero value.
Thus,
dim i as integer
i = 1
if i = True then ' will evaluate as true
...
if (NOT i) = True then ' will also evaluate as true
Since the NOT operator does a binary XOR on numeric data types, it converts a numeric value of 1 (one) to a value of -2, which is considered as true, thus we have the interesting condition of:
if (i = True) and ((NOT i) = True) then ' yep - both are true
October 28, 2015 at 12:17 pm
print CAST('True' as bit)
print CAST('False' as bit)
October 28, 2015 at 3:52 pm
Jeff Moden (10/26/2015)
SQLBill (10/26/2015)
The way I would determine which to use (Active or Inactive) would depend on how I am going to query the data. I use "pseudo code" to determine what is important.Am I asking:
Who (what) is active? or Who (what) is inactive?
Is this person/thing active? or Is this person/thing inactive?
The problem that I have with that is then you have a "1" for something that's Inactive. To me, "Inactive" means "False" because if it's "Inactive", then its NOT active. I've seen folks get confused over less, especially if they're in a hurry during troubleshooting of an urgent problem.
Jeff,
I get that, and we may have to "agree to disagree", but the way I look at it is this way:
Client: I need to know what objects are inactive.
My pseudo query would be: Return all objects where inactive = True.
SQL Query: SELECT objects FROM table WHERE inactive = 1
As I said, I believe that how the data is queried should drive how the database/tables are set up.
But, there would be nothing wrong with: SELECT objects FROM table WHERE active = 0
It's just a preference.
-SQLBill
October 28, 2015 at 3:58 pm
Other client: I need to know which objects are not inactive. You can make the display text say any dang thing the client wants to see. That doesn't mean you design databases that way.
October 29, 2015 at 2:47 pm
GeorgeCopeland (10/28/2015)
Other client: I need to know which objects are not inactive. You can make the display text say any dang thing the client wants to see. That doesn't mean you design databases that way.
True. There's arguments both ways (as this thread has shown).
-SQLBill
November 3, 2015 at 12:11 pm
In our environment, for our Type 2 Dimensions, we use [CurrentRowFlag] as a SMALLINT.
We use 1 for current row, 0 for inactive, and -1 for deleted (from Source).
This allows us to track when a particular row was hard-deleted from the source, yet still keep a record of it in our Data Warehouse.
November 3, 2015 at 12:17 pm
In our environment, for our Type 2 Dimensions, we use [CurrentRowFlag] as a SMALLINT.
We use 1 for current row, 0 for inactive, and -1 for deleted (from Source).
I'm moving to a similar system, but the fact and dimension tables will be on views that exclude the deleted rows. They would only be accessible by direct query.
November 18, 2015 at 3:03 pm
RWillsie (10/23/2015)
U.S.A.F. Instructors pounded it into my head many years ago to never use "negative logic"
...an effort doomed from the start.
Viewing 15 posts - 106 through 120 (of 129 total)
You must be logged in to reply to this topic. Login to reply