Introducing the Quad State bit flag



  • When I think about posting the many wtf's I come across in the system I have inherited, I normally find that snoofle has posted nearly verbatim the exact same scenario. For a time I actually thought we worked in the same company. Today however I came across something I think is truly unique. A Quad state permission flag.

    In the web app I inheritied there is the concept that each section of the app has a series of "Tools" such as the delete product tool, or the change you username tool. As a user you either have a particular tool or you dont. There is nothing more complex than that. To have a tool you must either have it connected directly to your user profile OR be a member of a group which has this tool.

    If I were to design this in under 5 minutes I would probably come up with something like this.

    •  userTable
      • userId PK ident
      • username
      • etc
    • GroupTable
      • groupId PK ident
      • groupName
    • ToolsTable
      • toolId PK ident
      • toolName
    • xWalk_User_Groups
      • groupId FK composite PK
      • userId FK composite PK
    • xWalk_Group_Tools
      • groupId FK composite PK
      • toolId FK composite PK
    • xWalk_User_Tools
      • userId FK composite PK
      • toolId FK composite PK

    In order for a user to have a tool in this setup all they would need is a matching toolId/userId in the xwalk_user_tools or be in a group with a groupId/toolId reference in the xWalk_Group_Tools table.

    Would you like the see what my predecessor came up with?

    • userTable
      • userId ident
      • username
      • etc
    • GroupTable
      • groupId ident
      • groupName
    • ToolsTable
      • toolId ident
      • toolName
    • xWalk_User_Groups
      • ID ident
      • groupId
      • userId
    • xWalk_Users_Group_Tools
      • ID iden
      • groupId
      • userId
      • toolId
      • allowedAccess bit null

    So lets look at the minor WTF's first

    1. UserId in userTable is  ident but not explicitly defined as PK
    2. GroupId in groupTable is  ident but not explicitly defined as PK
    3. ToolId in toolsTable is  ident but not explicitly defined as PK
    4. GroupId and UserId in xWalk_User_Groups not set as FK relationship due to 1 & 2 and are not marked as a PK with only the ID ident column to make unique (hence many many duplicates)
    5. ID in xWalk_Users_Group_Tools is ident but not explicitly defined as PK
      1. This column should not even be needed
    6. Because of 1,2 & 3 the userId,groupId and toolId in xWalk_Users_Group_Tools cannot be defined as FK
    7. xWalk_Users_Group_Tools is dual purpose taking the place of xWalk_Group_Tools & xWalk_user_tools

    Now lets get to the part that really blew my mind.

    As defined by this table structure and the application code. If we assume for simplicities sake that a user is not a member of any group, then there are 3 conditions where a user does not have a tool, and one condition where the user does.

    These are:

    1. If no record exists in xWalk_Users_Group_Tools with that userId and toolId -----> Tool Access Denied
    2. If a record exists in xWalk_Users_Group_Tools BUT the allowedAccess bit flag is null ----------> Tool Access Denied
    3. If a record exists in xWalk_Users_Group_Tools BUT the allowedAccess bit flag is FALSE ----------> Tool Access Granted
    4. If a record exists in xWalk_Users_Group_Tools BUT the allowedAccess bit flag is TRUE ----------> Tool Access Denied

     Yup. A tool access permission that should have 2 states, exists or does not exist is actually 4 states AND in order for a user to have a tool they must have a record in the xWalk_Users_Group_Tools table for that tool where the allowedAccess bit flag is set to FALSE.

     



  • Some (maybe most) people just don't get how relational databases are supposed to work.

    Incidentally, what's the deal with the xWalk prefixes? Does the x stand for cross/Cartesian product or something?


  • Garbage Person

    @arotenbe said:

    Some (maybe most) people just don't get how relational databases are supposed to work.

    Incidentally, what's the deal with the xWalk prefixes? Does the x stand for cross/Cartesian product or something?

    xWalk stands for "crosswalk." In my enterprise, "crosswalk" is projectmanagerese for "Something in Microsoft Excel with only loose structuring and nigh-unimportability, but enough information that nobody is ever going to agree to rekeying it into a sensible database structure manually, so you need to bodge some spectacular import SQL that does unspeakable things with dynamic SQL, union all, CTE's, pivot, unpivot, and occasionally XML. Additionally, this import SQL will work only for this version of the document. It will be refreshed 45 minutes before every hard stop deadline." 

    Similarly, "matrix" means the same thing, except it has vast quantities of unrelated information sprinkled about its structure, so you have to manually clean the irrelevant shit out before running it through the bodge.

    In the end, both of these things usually boil down to associative tables, so apparently in the OP's enterprise, they've decided to code in a way that they can communicate directly with the PM's. I tried this technique once, and ended up with tblMatrix (ugh hungarian). 15 minutes after go-live, everybody forgot what "The Matrix" meant (there was only one on the project), and I now have to call it "The big ugly spreadsheet"

     

     



  •  @arotenbe said:

    Some (maybe most) people just don't get how relational databases are supposed to work.

    Incidentally, what's the deal with the xWalk prefixes? Does the x stand for cross/Cartesian product or something?

    xWalk short for cross walk. Just a naming convention I use to ID tables that form many to many relationships between other tables. I find it less typing than terming them something like junction_{name} tables. The actual table names by the way were somewhat less descriptive but I had to obfuscate for obvious reasons.

    The best part of this whole saga is I checked and this guy used to be a DBA. (I wonder why he is'nt any more.)

     



  • @codefanatic said:

    xWalk short for cross walk. Just a naming convention I use to ID tables that form many to many relationships between other tables. I find it less typing than terming them something like junction_{name} tables. The actual table names by the way were somewhat less descriptive but I had to obfuscate for obvious reasons.

    I find the presented naming conventions and the fact that you don't call them out as a WTF to be a bigger WTF than the one you presented. Sure, FALSE in the allowedAccess flag meaning "has access" is bad, but the behavior for NULL and missing are sane. Instead of calling a junction table "junction" or "xWalk", I simply name them after what they mean. For example, xWalk_User_Groups would be GroupMembers. Also, suffixing a table with "Table" is a horrible idea.



  •  @Jaime said:

    @codefanatic said:

    xWalk short for cross walk. Just a naming convention I use to ID tables that form many to many relationships between other tables. I find it less typing than terming them something like junction_{name} tables. The actual table names by the way were somewhat less descriptive but I had to obfuscate for obvious reasons.

    I find the presented naming conventions and the fact that you don't call them out as a WTF to be a bigger WTF than the one you presented. Sure, FALSE in the allowedAccess flag meaning "has access" is bad, but the behavior for NULL and missing are sane. Instead of calling a junction table "junction" or "xWalk", I simply name them after what they mean. For example, xWalk_User_Groups would be GroupMembers. Also, suffixing a table with "Table" is a horrible idea.

    Seriously? You find it more important to critic a naming convention than the fact that this is both a DUAL PURPOSE table (which no table should EVER be) with no enforced foreign key relationships and where there are THREE count them ONE TWO THREE different ways to identify a permission is denied and the one way where a permission is granted when the permission flag is set false?

    Let me state it so its clear. THERE SHOULD BE NO PERMISSION FLAG AT ALL! Either the user record is linked to the tool or it is not.

     

    How about this. Write me a query to identify all users who do not have a tool x. It will end up something like this

    GET ALL users who

    do not have a record in the xWalk table for this tool,

    OR who have a record for this tool but where permission flag is null

    OR who have a record for this tool but where permission flag is true


    Then come back to me and tell me which is more important. What a table is named, or how it is actualy constructed. If you still can't see what the bigger wtf is here then I know I dont want to see any database you have put together.


  • Discourse touched me in a no-no place

    @codefanatic said:

    Then
    come back to me and tell me which is more important. What a table is
    named, or how it is actualy constructed.
    Generally the two go together. If you can't coherently name your tables anything sensible, what those tables contain is hardly going to be the epitome of database design.



  • @PJH said:

    @codefanatic said:
    Then come back to me and tell me which is more important. What a table is named, or how it is actualy constructed.
    Generally the two go together. If you can't coherently name your tables anything sensible, what those tables contain is hardly going to be the epitome of database design.
     

    Yes. But what is not sensible or coherent about this naming convention? You may not agree with it, but at least is is consistent and has meaning.

    xWalk_table1_table2

    It readily identifies that the purpose of the table is to relate two other tables together.

    Its not like the table has a meaningless prefix like 'table_'. A name/label should impart information about what the purpose of the table or variable is. For example a prefix of table imparts no information to the developer about the purpose of the table and hence has no value, where as xWalk (by our teams agreed upon convention) imparts to all developers on the team that the purpose of this table to to form n-n relationships between other tables.

    Even if you dont agree with the convention it really is such a unimportant part of the overall issue that I truly am amazed that people are more interested in it than how terrible the actual database design is. One affects how the system performs the other does not.



  •  Actually, the presence of a flag can have its utility when you want to be able to suspend access without removing it altogether. Doesn't excuse the NULL, though.



  • @codefanatic said:

    How about this. Write me a query to identify all users who do not have a tool x. It will end up something like this

    GET ALL users who

    do not have a record in the xWalk table for this tool where permission flag is false

    OR who have a record for this tool but where permission flag is null

    OR who have a record for this tool but where permission flag is true


    Then come back to me and tell me which is more important. What a table is named, or how it is actualy constructed. If you still can't see what the bigger wtf is here then I know I dont want to see any database you have put together.

    That was lame.


  • Garbage Person

     While we're here, I've ended up implementing this sort of mechanism fairly often. What I've arrived at is a design pattern like this:

    SecurityItem
    id (PK)
    name

    Users
    securityItemId (PK, FK.SecurityItems)

    Groups
    securityItemId (PK, FK.SecurityItems)

    UserGroups
    userSecurityItemId (PK, FK.users)
    groupSecurityItemId (PK, FK.groups)

    Permissions
    permissionID (PK)
    name

    SecurityItemPermissions
    permissionID (PK, FK.permissions)
    securityItemId (PK, FK.SecurityItems)

    Inheritance: It ain't just for OOP.

    This way, you can actually easily extend things later to allow nested groups (if you can deal with the insanity of having to unwind them to use them)



  • @codefanatic said:

  • If a record exists in xWalk_Users_Group_Tools BUT the allowedAccess bit flag is FALSE ----------> Tool Access Granted
  • If a record exists in xWalk_Users_Group_Tools BUT the allowedAccess bit flag is TRUE ----------> Tool Access Denied
  •  

    That correct, or a typo?

    It really seems that a simple query to see if allowedAccess is TRUE for that particular user is all that's required. All other cases would return "denied".

    As a minor design WTF aside... I'd have also enforced permissions against groups and not individuals, so manage permissions by changing group membership. It's how many other systems do it.

     



  • @Cassidy said:

    @codefanatic said:

  • If a record exists in xWalk_Users_Group_Tools BUT the allowedAccess bit flag is FALSE ----------> Tool Access Granted
  • If a record exists in xWalk_Users_Group_Tools BUT the allowedAccess bit flag is TRUE ----------> Tool Access Denied
  •  

    That correct, or a typo?

    It really seems that a simple query to see if allowedAccess is TRUE for that particular user is all that's required. All other cases would return "denied".

    As a minor design WTF aside... I'd have also enforced permissions against groups and not individuals, so manage permissions by changing group membership. It's how many other systems do it.

     

     

    No thats not a typo. Thats what makes it so screwed up. And there are multiple different queries scattered in the application code that check this table so its not a simple fix.

    If you want to see if a user has a tool you query for if the record exists and the flag is false.

    And if you want a list of users who do not have access to a given tool then you either do a query to get users who are not in list of users who do have it, or you get users who dont have association or have association but flag is null or who have association and flag is true.

    The fact of the matter is that all should need is for the association record to exist or not.

     



  • @rootkit said:

    @codefanatic said:

    How about this. Write me a query to identify all users who do not have a tool x. It will end up something like this

    GET ALL users who

    do not have a record in the xWalk table for this tool where permission flag is false

    OR who have a record for this tool but where permission flag is null

    OR who have a record for this tool but where permission flag is true


    Then come back to me and tell me which is more important. What a table is named, or how it is actualy constructed. If you still can't see what the bigger wtf is here then I know I dont want to see any database you have put together.

    That was lame.

     

    I dont know what is so hard to understand.The question I asked was "identify all users who do not have a tool x" for example doing sort of security audit.

    The way the application works is that a user either has a tool or they do not. It should be a simple association of userId to toolId. If the assocation exists then the user has the tool, if it doesnt then they dont.

    But in this bizaro table the concept of a nullable permission bit flag with an inverted value creates this mess. norecord -> denied, record nul -> denied, record false -> granted, record true -> denied

     



  • @Medinoc said:

     Actually, the presence of a flag can have its utility when you want to be able to suspend access without removing it altogether. Doesn't excuse the NULL, though.

     

    You're right it would IF the application had the concept of suspended permissions. However it doesnt and there is a freeze on new development on this app so there probably never will be. Also the fact that the column is named allowedAccess but the value must be FALSE to grant access.

     



  • @codefanatic said:

    @rootkit said:

    @codefanatic said:

    How about this. Write me a query to identify all users who do not have a tool x. It will end up something like this

    GET ALL users who

    do not have a record in the xWalk table for this tool where permission flag is false

    OR who have a record for this tool but where permission flag is null

    OR who have a record for this tool but where permission flag is true


    Then come back to me and tell me which is more important. What a table is named, or how it is actualy constructed. If you still can't see what the bigger wtf is here then I know I dont want to see any database you have put together.

    That was lame.

     

    I dont know what is so hard to understand.The question I asked was "identify all users who do not have a tool x" for example doing sort of security audit.

    The way the application works is that a user either has a tool or they do not. It should be a simple association of userId to toolId. If the assocation exists then the user has the tool, if it doesnt then they dont.

    But in this bizaro table the concept of a nullable permission bit flag with an inverted value creates this mess. norecord -> denied, record nul -> denied, record false -> granted, record true -> denied

     

    I do agree that the whole situation is a mess. No doubt about that.

    However, check my modifications again to find that this creates a much simpler query with equal results. Basic set theory applied:

    • Permission: EXISTS (SELECT * FROM nastyWTFtable WHERE userid = user.id AND permission = FALSE) --> TRWTF
    • No permission: the reverse, ergo NOT EXISTS(verbatim copy of above)

    QED.



  • Seeing as how people go crazy over naming but are willing to forgive and even make exuses but bad design, maybe I should not bother mentioning todays gem found in the same database.

    A producs table where the productName field is defined as varchar(1000) but the form in the application for entering products only allows 25 characters.



  • @rootkit said:

    @codefanatic said:

    @rootkit said:

    @codefanatic said:

    How about this. Write me a query to identify all users who do not have a tool x. It will end up something like this

    GET ALL users who

    do not have a record in the xWalk table for this tool where permission flag is false

    OR who have a record for this tool but where permission flag is null

    OR who have a record for this tool but where permission flag is true


    Then come back to me and tell me which is more important. What a table is named, or how it is actualy constructed. If you still can't see what the bigger wtf is here then I know I dont want to see any database you have put together.

    That was lame.

     

    I dont know what is so hard to understand.The question I asked was "identify all users who do not have a tool x" for example doing sort of security audit.

    The way the application works is that a user either has a tool or they do not. It should be a simple association of userId to toolId. If the assocation exists then the user has the tool, if it doesnt then they dont.

    But in this bizaro table the concept of a nullable permission bit flag with an inverted value creates this mess. norecord -> denied, record nul -> denied, record false -> granted, record true -> denied

     

    I do agree that the whole situation is a mess. No doubt about that.

    However, check my modifications again to find that this creates a much simpler query with equal results. Basic set theory applied:

    • Permission: EXISTS (SELECT * FROM nastyWTFtable WHERE userid = user.id AND permission = FALSE) --> TRWTF
    • No permission: the reverse, ergo NOT EXISTS(verbatim copy of above)

     

    QED.

     

    Ah my appologies. I should have read it closer.

    Yeah thats exactly what I ended up doing. Im a believer though that you should be able to understand how data elements relate to each other just by looking at a diagram of the tables. In this case a new dev would have to cross reference to application code to figure out these rules.

     



  • @codefanatic said:

    A producs table where the productName field is defined as varchar(1000) but the form in the application for entering products only allows 25 characters.

    That's... not bad at all. I'd actually say the biggest problem with that is that your product name field is too small.


  • Trolleybus Mechanic

    @blakeyrat said:

    @codefanatic said:
    A producs table where the productName field is defined as varchar(1000) but the form in the application for entering products only allows 25 characters.

    That's... not bad at all. I'd actually say the biggest problem with that is that your product name field is too small.

     

    User Bug Report: "Cannot enter a a twenty-six character item in the product field"

    Developer: I can add 'Twenty-Six Character Item' to product field. Cannot reproduce. Closing, wontfix.



  • @blakeyrat said:

    @codefanatic said:
    A producs table where the productName field is defined as varchar(1000) but the form in the application for entering products only allows 25 characters.

    That's... not bad at all. I'd actually say the biggest problem with that is that your product name field is too small.

     

    Do some research on page sizing in SQL Server and then you'll see why this is very bad. And this is only one instance of this kind of thing I've found. Ive seen varchar 2000,600,8000 just random large numbers with no relation to how big the expected data is.

    Hint: This is roughly equivalent to defining an office rule that every piece of paper you store on a shelf goes in its own ring binder. Yes it works but very soon you need to add a new shelf, and searches (table scans) take a lot longer than they should.



  • @codefanatic said:

    Do some research on page sizing in SQL Server and then you'll see why this is very bad.

    Oh, sorry, there was required reading?



  • @blakeyrat said:

    @codefanatic said:
    Do some research on page sizing in SQL Server and then you'll see why this is very bad.

    Oh, sorry, there was required reading?

     

    Actually I wish it was required reading for anyone who builds databases. I blame database 101 classes that either dont bother to say (or dont explains why) you should always use the smallest data size you can.

    Storage may be cheap, but big table scans are not.



  • @codefanatic said:

    No thats not a typo. Thats what makes it so screwed up.
     

    @codefanatic said:

    Also the fact that the column is named allowedAccess but the value must be FALSE to grant access.

    Has anyone come up with a reason for this yet? I guess you'll find out during tomorrow's night shift (yesterday morning).

    @codefanatic said:

    And there are multiple different queries scattered in the application code that check this table so its not a simple fix.

    STORED PROCEDURES, BITCHES!

     



  • @Cassidy said:

    STORED PROCEDURES, BITCHES!

    Unless your working in Oracle and you need to return a table of results. Then it becomes a huge pain for some reason.

    You have to create types and tables type of the type, put them in packages for best practices, "manually" populate them in a PL/SQL for loop running off a cursor, then hope your client code's drivers are robust enough to handle it. I don't know why it's so darn complicated. I mean, besides it being Oracle.



  • @Xyro said:

    @Cassidy said:
    STORED PROCEDURES, BITCHES!
    Unless your working in Oracle and you need to return a table of results. Then it becomes a huge pain for some reason.

    Why would they do that, I mean besides the fact that they hate everyone and want to watch the world burn?  I'm so glad I don't need to deal with Oracle torture devices databases.


  • Considered Harmful

    @codefanatic said:

    Write me a query to identify all users who do not have a tool x. It will end up something like this

    select foo, bar from users where not userid in (
       select userid from xwalk where perm = 0 and tool = 'x'
    )
    


  • @Lorne Kates said:

    Developer: I can add 'Twenty-Six Character Item' to product field. Cannot reproduce. Closing, wontfix.

    And of course TRWTF is a developer who confuses WONTFIX with NOTABUG/INVALID or WORKSFORME state...

    BTW what happens if a user has four database rows for a tool, one being true, one being false and two being NULL? :D Or those four rows are for four groups he is a member in?

    Having a denied flag makes partial sense if it is implemented the way as it is in Microsoft ACLs. If you allow right for group A and deny for group B, a member of both groups will get the right denied... But I'm pretty sure that's not how it was implemented (at least not consistently).



  • @Cassidy said:

    STORED PROCEDURES, BITCHES!

    Wouldn't have helped. They obviously don't know how to follow the DRY principal in the app. The same people would have duplicated the query by creating a procedure for each case where code needs to get this data. You know you have fallen into this particular hell when the stored procedure naming convention is APP_PAGE_FUNCTION. I manage several apps that do this.



  • @mihi said:

    BTW what happens if a user has four database rows for a tool, one being true, one being false and two being NULL? :D Or those four rows are for four groups he is a member in?
     

    That depends on which part of the app you are trying to access as like i said there are different implementations of the query to establish if the permission is granted in different parts of the app.

    I can only dream about things like stored procedures as I am the only person in the whole department who even knows how to write them. I tried to introduce them in a meeting once and was shot down for trying to bring in "unnecessary complexity".



  • @codefanatic said:

    @blakeyrat said:

    @codefanatic said:
    Do some research on page sizing in SQL Server and then you'll see why this is very bad.
    Oh, sorry, there was required reading?
     

    Actually I wish it was required reading for anyone who builds databases. I blame database 101 classes that either dont bother to say (or dont explains why) you should always use the smallest data size you can.

    Storage may be cheap, but big table scans are not.

    SQL Server store varchars as a two-byte length, one bit for nullability and the actual data, non-padded.  'Hello' in a varchar(1000) columns takes the same space as 'Hello' in a varchar(10) column.


  • @codefanatic said:

    the productName field is defined as varchar(1000)
     

    Is there any product that actually has 1000 characters in the name, or did someone wrongly label the productDescription field?

    That seems like an overkill to me, but then I ain't seen the original design specs.



  • @Jaime said:

    SQL Server store varchars as a two-byte length, one bit for nullability and the actual data, non-padded.  'Hello' in a varchar(1000) columns takes the same space as 'Hello' in a varchar(10) column.
     

    Oops you're right, for some reason I kept thinking fixed length like char. So really not that bad. Of course its still pretty stupid to allow 1000 characters in database but only 25 or so on the form that enters the data. Hopefully if they ever add other data entry methods down the line for product that they remember the rule set in the existing form and not just go by the field length in the database.



  • @codefanatic said:

    xWalk_Users_Group_Tools is dual purpose taking the place of xWalk_Group_Tools & xWalk_user_tools

    So, how do you specify whether a particular row in this table is mapping a user to a tool or a group to a tool? Is the user id set to null or zero when it's for a group and vice versa, or something?



  • @ekolis said:

    @codefanatic said:
    xWalk_Users_Group_Tools is dual purpose taking the place of xWalk_Group_Tools & xWalk_user_tools

    So, how do you specify whether a particular row in this table is mapping a user to a tool or a group to a tool? Is the user id set to null or zero when it's for a group and vice versa, or something?

    Like everything else in the system its mostly by accident.

    Basically how it works is if the groupid is matched to the tool and the table linking users to group matches then permission is granted and the userid is ignored. If not then the userid Col is checked for a match.Of course remembering to check that the permissionGranted flag is set to false.

    This would not be so bad except that it is implemented inconsistently. Sometimes you will find a single record with userid,groupid & toolid set. Other times you will find 2 records. 1 with groupid and toolid set and the other with userid and toolid set. And because of the nullable bit flag and the fact that there are no defined pk rules sometimes you will find something like this which is the bug I was trying to identify where a user could not use a certain tool.

    All 4 of these records existed at the same time for this user

    1: toolid set ,groupid set, permissionflag null

    2: toolid set, groupid set, permissionflag true

    3: toolid set, groupid set, permissionflag false

    4: toolid set, userid set, groupid set, permissionflag true

    I would have liked to blow away records 1-3 but there was no way to know for certain if this would grant or deny access to this tool for other users in this group and if they should or should not have access (and the person in charge of setting these permissions has no records of who really should have access to this tool).

    In the end editing record 4 to permissionFlag == false solved the logged problem but the real problem still exists unfixed to this day. And my request to put this on the list of bugs to fix was denied.


Log in to reply