Varchar not null default null



  • One of the pearls of our database: 

    [URL=http://img529.imageshack.us/my.php?image=varcharnotnulldefaultnuze1.jpg][IMG]http://img529.imageshack.us/img529/3063/varcharnotnulldefaultnuze1.jpg[/IMG][/URL]

    Like in many other fields, "B_" means boolean but the field is not boolean: is a Varchar2(1) that can contain the strings "S" (for "Yes", "Sí" in Spanish) and "N" (for "No"). That alone wouldn't be a WTF if the application used a boolean in the data objects and made the conversion to the proper String during saving or retrieval... but the application is actually moving the string everywhere around and the <logic:notEmpty ... ><logic:equals ... value="S"> are not uncommon.

    Curiously, the two fields shown in the image means "mental disorder" and "mental deficiency".
     


  • Considered Harmful

    Null is the default default value when no default value is specified.  If the column is not null, then this results in an error if the field is not specified.  It may sound strange, but it's simply a way to require the field to be explicitly set.  Of course, it may have been carelessly where a default of 'N' was desirable, but I don't know enough about the system to say either way.

    Personally, I hate the use of characters for boolean values.  The value could possibly be "S", "s", "N", "n", "Y", "y", or even "?".  So then you have to add a constraint on top of that to keep the data sane, and still you end up having logic built on top of it so it becomes difficult to change (e.g., to English "Y").  Of course, I don't use Oracle, and I hear there's an Oracle Haters' Club on these forums; so, I'll assume it's the RDBMS's fault in this case.



  • You know, if you used some globalization scheme, you could use booleans, and the Si-No / Sim-Não / Oui-Non / Yes-No etc. parsing would be dealt with automatically.



  • what it should do is, print out a page containing either "S" or "N" onto a wooden table, which is then later filed into a filing cabinet at the end of the day.



  • Personally, I would print a complete dump of the record ID and "boolean" fields, take a picture of them on a wooden table, fax the pictures to another department that would then have people re-enter the data, just to be sure that it's correct. 

    Then the printouts could be filed, loaded onto a truck, and moved to storage.  



  • I think the injoke can stop now, we have reached and passed the limit for funny.



  • Please do not inform the designers about the potential of blob...


    No please do, also encourage them to exercise in the middle of shark infested waters.



  • What, you aren't storing XML in the DB?



  • <boolvalue><yesno>Y</yesno><yesno active="Y">N</yesno></boolvalue>



  • @Kain0_0 said:

    Please do not inform the designers about the potential of blob...

    No please do, also encourage them to exercise in the middle of shark infested waters.

    It's already too late:

      /**
       * Contains internally the "comment" field.<br>
       * Aditionally, it contains other fields in the form
       * <code>"{key=value[,key=value]}"</code><br>
       */
      private String comment;

    ...and...

          int left = mcomment.indexOf("{");
          int right = mcomment.lastIndexOf("}");
          String textComment = null;
          if ((left == -1) || (right == -1) || (left > right)) {
            textComment = mcomment;
           
          } else {
            textComment = mcomment.substring(0, left) + mcomment.substring(right + 1);
            String parameters = mcomment.substring(left + 1, right);
            String[] parameter = parameters.split(",");
            for (int i = 0; i < parameter.length; i++) {
              if ((parameter[i] == null) || (parameter[i].length() == 0)) {
                continue;
              }
              String[] keyValue = parameter[i].split("=");
              if ((keyValue.length != 2) || (keyValue[1].length() == 0)) {
                continue;
              }
              // ...retrieves field values
              if (keyValue[0].equals(INTERNAL_KEY_COORDINATOR)) {
                this.coordinator = keyValue[1];
                isCoordinatorAsigned = true;
              }
              if (keyValue[0].equals(INTERNAL_KEY_ROLE)) {
                this.role = keyValue[1];
              }
            }
          }
         
          this.setComment(textComment);
        }


    Yeah, it seems that creating two new fields was a "too correct way" of doing that.

    Fortunately, that methods are in the class that interfaces the DB with the rest of the code, so all the code can act as if the two fields where there. I don't want to think what will happen when someone inserts a opening bracket in the comment... :S 


Log in to reply