Positive or Negative

  • Jeff Moden (10/24/2015)


    +1000 to the date thing, Kim. I've to often seen a (simplified for sure) "StartDate" and "EndDate" that's also accompanied by an "IsActive" column which "IsInsane" to me. 😛

    Yep. I just ran into a situation where a developer apparently decided they needed a "StatusCode" column to determine whether a record was "InProgress", "Submitted", "Approved", or "Verified". Of course, there is also a "SubmittedOn" datetime, an "ApprovedOn" datetime, and a "VerifiedOn" datetime, which would suffice to determine the status. And - surprise, surprise - somewhere in the application they occasionally (and very rarely!) manage to set the StatusCode to a value inconsistent with the various datetime values.

    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?

  • 0 = True is not just dumb, it is malicious. I want programmers like this far from my team.

  • While I tend to be affirmative in initial design (IsActive), the correct and most efficient use of a flag is for marking exceptions. In almost any system, there is little or no need to store a default, and I would always recommend extending this to bit flags.

  • GeorgeCopeland (10/26/2015)


    0 = True is not just dumb, it is malicious. I want programmers like this far from my team.

    Dumb (or just inexperienced) programmers are hired to initially develop databases, while those of us who actually know how databases should be architected, we're just kept on staff to mop up the mess. Often times I feel like a nutritionist working for McDonalds; I'm turned to for advice, but at the end of the day I have little influence over the end product.

    This pattern is repeated so often in the IT, it can be assumed to be the industry standard for how software and databases are developed. Anyone here feel the same?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Programmers who ignore their DBA consultants are idiots.

  • 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. 🙂

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • 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.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • I'm going to agree with Eric on his last point - even though I'm a software developer!

    However, I'm going to add that it works both ways. Trying to write complex business rules into a database with little or no automated testing capability doesn't work so well either. You've got to pick the right tool for the right job.

    What you need is the right mix of people in a team (developers, tester, business analysts, DBAs, etc.) that know and understand what the strengths are of each layer in the solution architecture. Some stuff works best at the database layer, some stuff works best at the application layer, some stuff works best at the UI layer. It all depends.

    It's about knowing what you're building and making sensible decisions along the way - if you can! 😉

  • Often times I feel like a nutritionist working for McDonalds; I'm turned to for advice, but at the end of the day I have little influence over the end product.

    Great analogy. I'm going to steal that line from time to time.

  • jeff.pendleton (10/26/2015)


    ...the correct and most efficient use of a flag is for marking exceptions...

    I strongly disagree with both of those notions especially since NULLs are a frequent cause of needing non-SARGable critieria and "OR"s.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • leroux 56837 (10/23/2015)


    I suggest it depends on intent. If the intent is that the user must explicitly acknowledge that it is active then I would go for the positive approach. If the records are normally inactive and only some become active then I would use the negative approach.

    I think it is bad practice to assume it implied anything about the default. What if you had a number of these columns and the default for one changed, would you rename that column throughout your code or live with an implication that people had got used to but didn't apply anymore?

  • 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?

    If the queries are commonly asking if it is inactive, then I want an inactive column where 1 is inactive.

    IMO, databases/tables/columns, need to be designed based on what information you want to get out of it. Then you determine what information needs to go into it.

    -SQLBill

  • SQLBill (10/26/2015)


    IMO, databases/tables/columns, need to be designed based on what information you want to get out of it. Then you determine what information needs to go into it.

    -SQLBill

    Absolutely correct! Some developers look at me as if I were crazy when I start asking end users to round up and get me all their current daily/weekly/monthly operational reports, but if you don't capture the information on the front end, it is a lot harder to add it later.

    It seems like too many developers focus on a "cool" front-end UI without making sure that they are capturing everything that will be needed later. There seems to be an attitude of "Reports? Those will be done later." (Yep - later - when we get to redesign the system to add the missing information.) And while I believe in Agile, it should not be used as an excuse to avoid proper requirements gathering up-front.

    If I can't take a customer's existing reports and see where all the data (including implied data) will be coming from in a new system, then it is time to raise the red flags.

    Maybe the new system will eliminate the need for some specific reports, but most of those existed for a reason.

  • 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.

Viewing 15 posts - 76 through 90 (of 129 total)

You must be logged in to reply to this topic. Login to reply