Forum Stats

  • 3,875,295 Users
  • 2,266,907 Discussions
  • 7,912,141 Comments

Discussions

New type BOOLEAN

123457

Comments

  • Andrew Moskevitz
    Andrew Moskevitz Member Posts: 2 Red Ribbon

    The need for this datatype is evidenced by the inconsistency in Booleans in Oracles own built in tables
    for example GV$TABLESPACE uses YES/NO
    V$OPTION uses TRUE/FALSE

    ALL_INDEXES USES both YES/NO and Y/N.

    And the ODP.Net Entity Framework Boolean is NUMBER(1,0) forcing 0=false and 1=true

    William RobertsonThorsten KettnerApexBinePeter Hraško
  • blessed DBA
    blessed DBA Member Posts: 218

    Good one for migration projects

    ApexBine
  • MarkStewart
    MarkStewart Member Posts: 23 Bronze Badge

    If dBase II for the IBM PC DOS o/s back in 1982 had a BOOLEAN data type, why can't Oracle?  It would sure save a lot of CHECK constraints. 

    Lukas EderApexBinePeter HraškoPeterG
  • Peter Hraško
    Peter Hraško Member Posts: 13 Blue Ribbon
    edited Aug 16, 2018 5:27AM

    If dBase II for the IBM PC DOS o/s back in 1982 had a BOOLEAN data type, why can't Oracle?  It would sure save a lot of CHECK constraints. 

    ... or spare me a lot of laughs from my colleagues for introducing a BOOLEAN_ENUM table in a few of my data models.

    Exactly! A native boolean data type could reduce many ambiguities+redundancies in check constraints or it could reduce many recursive FK checks against a boolean enum table upon DMLs. Not to mention all positives that've already been mentioned above.

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    Hi,

    As it seems that the need of boolean datatype is obvious for everybody, here is an interesting read which raises the point that boolean datatype is a shortcut for bad relational database design: https://vadimtropashko.wordpress.com/2010/09/16/on-boolean-datatype-in-sql-and-beyond/

    However, this is not a reason not to vote. JSON is also a shortcut for lazy database design and everybody likes it

    Regards,

    Franck.

    William Robertson
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    Hi,

    As it seems that the need of boolean datatype is obvious for everybody, here is an interesting read which raises the point that boolean datatype is a shortcut for bad relational database design: https://vadimtropashko.wordpress.com/2010/09/16/on-boolean-datatype-in-sql-and-beyond/

    However, this is not a reason not to vote. JSON is also a shortcut for lazy database design and everybody likes it

    Regards,

    Franck.

    His argument boils down to claiming that all Boolean values can be derived from other information (e.g. 'IS_CREDIT_WORTHY' just means 'CREDIT_RATING > 5'). I'm not convinced that is true.

    Sentinel
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Aug 21, 2018 6:27AM

    His argument boils down to claiming that all Boolean values can be derived from other information (e.g. 'IS_CREDIT_WORTHY' just means 'CREDIT_RATING > 5'). I'm not convinced that is true.

    I would say from a data modelling perspective, it is better to have a column "RISK ASSESSMENT" and the values being "CREDIT WORTHY" and "DECLINED". This makes it easy to add other more detailed risk assessments later on. I think this is possible for all kinds of boolean columns attributes.Typical things like an ISACTIVE flag. Why not have "STATUS" and "ACTIVE", "INACTIVE" as values. Maybe later you want to add "NEW" or "ARCHIVED".

    Having said that, I think we might find examples where a boolean type column is useful. Especially if it helps performance (combine several such columns)  or makes code (mostly column references in plsql code) better maintainable. I still believe the need for that is overrated.

    An interesting side point is, that a boolean type seems to emphasize the TRUE value more than the FALSE value. This can be a wanted side effect.
    To give an example. Image a column SEX with the typical values M=MALE, F=FEMALE (I know there are more values possible). A boolean version would be "isMALE". Which clearly favors the male entry. Obviously this doesn't make sense for SEX. But it might make sense for other attributes.

    Franck Pachot
  • [Deleted User]
    [Deleted User] Posts: 0 Silver Trophy

    I would say from a data modelling perspective, it is better to have a column "RISK ASSESSMENT" and the values being "CREDIT WORTHY" and "DECLINED". This makes it easy to add other more detailed risk assessments later on. I think this is possible for all kinds of boolean columns attributes.Typical things like an ISACTIVE flag. Why not have "STATUS" and "ACTIVE", "INACTIVE" as values. Maybe later you want to add "NEW" or "ARCHIVED".

    Having said that, I think we might find examples where a boolean type column is useful. Especially if it helps performance (combine several such columns)  or makes code (mostly column references in plsql code) better maintainable. I still believe the need for that is overrated.

    An interesting side point is, that a boolean type seems to emphasize the TRUE value more than the FALSE value. This can be a wanted side effect.
    To give an example. Image a column SEX with the typical values M=MALE, F=FEMALE (I know there are more values possible). A boolean version would be "isMALE". Which clearly favors the male entry. Obviously this doesn't make sense for SEX. But it might make sense for other attributes.

    I agree with your data modelling points, no question about that. But as has been pointed out before, Oracle's own implementation isn't always consistent. And it would be nice if in Spatial we could simply say

    where sdo_inside(geometry1, geometry2)

    instead of always having to write

    where sdo_inside(geometry1, geometry2)='TRUE'

    so a boolean type in the where-clause would be a nice to have. I'm still not sure about having it as a datatype for a column, for reasons I pointed out in my previous comment.

    Sven W.MarkusHohloch
  • User_P9WRF
    User_P9WRF Member Posts: 9 Red Ribbon

    Hopefully this will also extend to JDBC.  It's pretty crazy that although PL/SQL supports boolean, I can't call a function retuning boolean from Java...

    amadisWilliam Robertson