November 18, 2015 at 6:23 pm
SQLBill (10/28/2015)
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
Understood and I agree that we'll have to disagree. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2015 at 5:59 pm
Eric M Russell (10/26/2015)
below86 (10/26/2015)
GeorgeCopeland (10/26/2015)
Programmers who ignore their DBA consultants are idiots.I ignore my DBA every day, oh wait, we don't have a DBA. 🙂
Many organizations, even if they have good software engineers, just don't "get" database architecture. They don't understand that once you've crossed the boundary of designing tables, indexes, and SQL, you're in territory for which you may not be truly proficient. Multiple iterations of refactoring may work for object oriented software, where the functional implementation can be encapsulated and abstracted, but it doesn't work so well for database development.
Many DBAs just don't "get" database architecture: you will find DBAs (and DBA consultants) asserting that having any NULLable columns is always a bad thing, you should make them not null and define a default for the cases that would otherwise be null; asserting that you should declare FOREIGN KEY constraints just to provide documentation, but keep them unchecked - don't actually let the RDBMS enforce then because it wrecks performance; asserting that CHECK constraints are a bad thing because they amount to having business logic in the database; asserting that FOREIGN KEY constraints are a bad thing because they amount to business logic in the database; asserting that update and delete cascadeing on FK constraints are pure anti-relational heresy; asserting that non-atomic columns are OK because you can easily split them on the fly as and when you need to; asserting that INSTEAD OF triggers should never be used because they are unreliable; asserting that it is essential to begin table names with tbl, column names with col, view names with vw, and so on; and promoting all sorts of other utter nonsense. I've even known DBAs who claimed that it was no part of their job to tune queries or determine what indexes should exist to improve perfornace or get rid of indexes which were wasting space and crippling performance, because those were all developer tasks.
In fact until I came across SQLServerCentral and discovered that its contributors included a number of clearly competent people who called themselves DBAs I had an extremely jaundiced view of people who used that title, because every single one I had ever had contact with had been an arrogant (and therefor uneducatable) and utterly incompetent idiot. And many others had similar experiences - and many developers learnt to ignore their DBA consultants because over the years they had discovered that not ignoring resulted in their ending up with systems that never actually worked.
Tom
November 26, 2015 at 6:24 pm
jvinsonncsu (10/26/2015)
This discussion hit a new turn. I am a software engineer who is getting MCSA designation because I wanted to know the whole picture. I know better than to ignore our DBA. I don't believe too many of my counterparts feel the same way though. I have seen it first hand. Working together you get a much better product and truthfully I use SQLite automation for column etc any time I can. I mean come on. Awl server agent rocks. Particularly with SSIS packages.I will pose a new question: Why do so few software engineers/developers try to learn everything they can about the deep workings of SQL server?
Not biased one way or the other. I am just curious.
I think I know the answer to that one: it's a result of overspecialisation, which is largely driven by sterotyping by recruiters. A developer needs to be not just a developer but a C++ developer or a C# developer of a Java developer or whatever kind of developer the recruiter is looking for, so time spent on looking at SQL Server internals won't buy employability in the market for developers. And looking at the internals of SQL Server would sound to a profesional recruiter (normally someone who hasn't a clue how many wheels a computer has or whether it runs on diesel or coal - believe me, when recruiting people I've had to deal with some of these recruitment "professionals" and they really haven't a clue) like someone who was looking at the internals of Microsoft's C# compiler - they wouldn't have a clue that understanding SQL Server internals would be of much greater use to an SQL developer than looking at the compiler's workings would have for a C# developer.
Tom
November 26, 2015 at 6:31 pm
Jeff Moden (10/26/2015)
Kim Crosser (10/25/2015)
It wouldn't be so bad if the "StatusCode" column were a computed column based on the datetime columns, but the developers don't like logic embedded in the database.Now I get to do some detective work and find the coding error by "grepping" my way through lots of application code, looking for the path that sets or fails to update the StatusCode column correctly. Did I mention the business logic is distributed through lots of different modules in a combination of COM+, .Net, VB, ASP, and JScript?
You've managed to hit a serious pet peeve of mine. Why would any company allow "developers" to define where business logic that affects the database to stipulate where that logic should be? Sorry... rhetorical question. We all know why that happens.
I would have a simple approach to Kim's problem: inform management that the only reasonable way I - as a database person - could resolve this problem would be to create a trigger which would cause an app failure when a modification was made to the database that introduced this inconsistency. It would then be possible to determine which part of which app was doing what when the failure occurred. The alternative would be to have the application developers do the work necessary to discover where their code was doing the wrong thing - perhaps it would be possible for them to do this in a non-disruptive manner.
Tom
November 26, 2015 at 7:43 pm
Gary Varga (10/28/2015)
Ignoring COM's implementation of boolean as -1 and 0, I think that every representation of a boolean I have seen has used 0 for false and 1 for true in its implementation. I would expect any IT literate person to know this.
Why do you think that to be IT literate one must know that you have a very narrow knowledge of the history or implementation of the BOOLean type in programming systems? Or were you intending to suggest something other than that? :hehe:
Back in the 60s there were all sorts of bools. 6 bit architectures mostly had 6 bit bools with octal(77) true and octal(00) false and other values error, but some had anything with the MS bit (sign bit on systems with 6 bit arithmetic) set as true and anything else as false, others nonzero true and zero false; 8 bit architectures were the same substituting hex(FF) for octal(yy) and hex(00) for octal(00). A couple of small but enterprising companies decided to extend the boolean type to have 64 or 256 values instead of just 2, with and, or, and not being bitwise operations, all 1s true and all 0s false and other values neither (and negating other values of course led to other values, so both NOT B and B could fail to be true for such values of B - something that SQL people should understand easily, although the C/C++/C#/Java mob probably won't) - this extension allowed character strings (arrays of bools) to work in a Fortran II dialect with that version of the BOOL type (and the Fortran IV standard was deliberately written so as to make this illegal, because the big boys hadn't implemented it and it would harm them if a Fortran with character strings were considered legitimate). Someone (I suspect it was Ivor Catt, but I could well be wrong) suggested looking at the whether there was a majority of 1 bits (true) or of 0 bits (false) or the same number of each (either error or undefined) but as far as I know that was never implemented by anybody. Neither did anyone back in those days, although they were implementing bool often as a two-valued type using 6 bits or 8 bits, suggest encoding true as EBCDIC (that was 8 bit - I've forgotten what the 6 bit character code was called) "T" or "Y" and fales as "F" or "N", that came rather later. Anyway, the number of language implementations using "flip all the bits" to get from true to false or vice versa has - over the approximately 6 decades since the first compilers that recognised boolean - been greater than the number flipping just the LS bit.
Tom
November 26, 2015 at 7:56 pm
A lot of interesting stuff here, maybe mostly a bit off topic, and I think I've probably commented on too much of it. So this post is just about the original question. The original question was restricted to a two-valued column, so although I usually find I want more that 2 values (maybe 5: Active, Suspended, Inactive, Cancelled, Error) sand so would have a character column rather than a bit I'll answer on th basis that I have a bit.
I would go positive. I'm not going to have a column called NotActive and the have to write NOT NotActive to indicated Active. So the column will be called Active, and it's value will be either 1 meaning "Active" or 0 meaning "Not Active" (and possibly, but not if the real world permits me to avoid it, the column will contain NULL meaning "I haven't a clue" - it will not, under any circumstances, contain NULL meaning "Not Applicable" - if I want a third value as opposed to not knowing athe value I won't use a bit since that only permits two values).
Tom
November 27, 2015 at 4:12 am
TomThomson (11/26/2015)
Gary Varga (10/28/2015)
Ignoring COM's implementation of boolean as -1 and 0, I think that every representation of a boolean I have seen has used 0 for false and 1 for true in its implementation. I would expect any IT literate person to know this.Why do you think that to be IT literate one must know that you have a very narrow knowledge of the history or implementation of the BOOLean type in programming systems? Or were you intending to suggest something other than that? :hehe:
Back in the 60s there were all sorts of bools. 6 bit architectures mostly had 6 bit bools with octal(77) true and octal(00) false and other values error, but some had anything with the MS bit (sign bit on systems with 6 bit arithmetic) set as true and anything else as false, others nonzero true and zero false; 8 bit architectures were the same substituting hex(FF) for octal(yy) and hex(00) for octal(00). A couple of small but enterprising companies decided to extend the boolean type to have 64 or 256 values instead of just 2, with and, or, and not being bitwise operations, all 1s true and all 0s false and other values neither (and negating other values of course led to other values, so both NOT B and B could fail to be true for such values of B - something that SQL people should understand easily, although the C/C++/C#/Java mob probably won't) - this extension allowed character strings (arrays of bools) to work in a Fortran II dialect with that version of the BOOL type (and the Fortran IV standard was deliberately written so as to make this illegal, because the big boys hadn't implemented it and it would harm them if a Fortran with character strings were considered legitimate). Someone (I suspect it was Ivor Catt, but I could well be wrong) suggested looking at the whether there was a majority of 1 bits (true) or of 0 bits (false) or the same number of each (either error or undefined) but as far as I know that was never implemented by anybody. Neither did anyone back in those days, although they were implementing bool often as a two-valued type using 6 bits or 8 bits, suggest encoding true as EBCDIC (that was 8 bit - I've forgotten what the 6 bit character code was called) "T" or "Y" and fales as "F" or "N", that came rather later. Anyway, the number of language implementations using "flip all the bits" to get from true to false or vice versa has - over the approximately 6 decades since the first compilers that recognised boolean - been greater than the number flipping just the LS bit.
I think I was being a little naïve and simple :doze:
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 27, 2015 at 11:15 am
TomThomson (11/26/2015)
I would have a simple approach to Kim's problem: inform management that the only reasonable way I - as a database person - could resolve this problem would be to create a trigger which would cause an app failure when a modification was made to the database that introduced this inconsistency. It would then be possible to determine which part of which app was doing what when the failure occurred. The alternative would be to have the application developers do the work necessary to discover where their code was doing the wrong thing - perhaps it would be possible for them to do this in a non-disruptive manner.
While that would at least give some additional information as to the processes involved where the error occurs, the production system is a 7x24 Public Safety system. Any trigger or other trap that causes a user-visible error and prevents an officer from completing his/her job isn't allowed. Besides - it would likely fire at 0300 on a Sunday, triggering help desk and other support calls. :w00t: So - back to debugging through code analysis.
November 30, 2015 at 10:58 am
Kim Crosser (11/27/2015)
TomThomson (11/26/2015)
I would have a simple approach to Kim's problem: inform management that the only reasonable way I - as a database person - could resolve this problem would be to create a trigger which would cause an app failure when a modification was made to the database that introduced this inconsistency. It would then be possible to determine which part of which app was doing what when the failure occurred. The alternative would be to have the application developers do the work necessary to discover where their code was doing the wrong thing - perhaps it would be possible for them to do this in a non-disruptive manner.While that would at least give some additional information as to the processes involved where the error occurs, the production system is a 7x24 Public Safety system. Any trigger or other trap that causes a user-visible error and prevents an officer from completing his/her job isn't allowed. Besides - it would likely fire at 0300 on a Sunday, triggering help desk and other support calls. :w00t: So - back to debugging through code analysis.
Your trigger (or auditing query) doesn't need to be visible to the user, it can just spam the devs until they fix it. Also talk to your boss about your job role... if you are a DBA, why are you getting stuck with debugging UI code?
November 30, 2015 at 12:36 pm
dietztm (11/30/2015)
Your trigger (or auditing query) doesn't need to be visible to the user, it can just spam the devs until they fix it. Also talk to your boss about your job role... if you are a DBA, why are you getting stuck with debugging UI code?
I am a Project Manager for the company that provides the software - I am not the DBA for the system, but I have way more DB experience than anyone else here (30+ years with Oracle, SQL Server, Ingres, DB2, Sybase, and other databases), so I am frequently the "go-to" guy for anything related to the database. (I also reverse-engineered the multiple databases used by the product to create a "data dictionary" for our customers, and I may actually know more about the database structure and relationships than the developers at this point.)
The agency has their systems locked down in a private intranet - no Internet access, so the only persons I can "spam" are myself and one other person - we are the only two vendor personnel authorized to access the system.
And - any changes to the live production system have to go through an extensive testing process before deployment - it took fewer hours for me to track down the problem using audit trails and the source code than it would have taken to go through the production update process to get some additional information about the bug.
The system DBAs (agency personnel) don't make any system modifications unless there was an emergency outage issue. All product problems are referred to me to get resolved either by myself or by the developers (located in a different State and with no authorized access to the Agency's systems). Since they don't have access, I usually try to track down the bug source and send them the information they need to make the change in their source repository and do a formal release.
Viewing 10 posts - 121 through 129 (of 129 total)
You must be logged in to reply to this topic. Login to reply