File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes  A question related to database table design Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark " A question related to database table design " Watch " A question related to database table design " New topic
Author

A question related to database table design

Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Hi ,

I am having a Query , in providing a boolean value inside a Database Table design

There is a requirement in our Application as we need to store a boolean value aganist customer Id .



Will it be a good approach if we use directly store true or false rather than using 1 or 0 respectively inside the column , to represent them inside Database table


Or will it be a good approach as to store 1 and 0 and make my Application convert them while displaying them ??
Please share your opinion on this . Thank you


Right now , I am thinking that we should use 1 or 0 in place of true or False , and convert them while displaying .
What do you suggest , please share a better approach



Save India From Corruption - Anna Hazare.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The normal pattern (if your database doesn't support a boolean type) is to use integers.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks Paul ,

As from the docs of Oracle it states that :

There is no BOOLEAN datatype in Oracle, as far as tables are concerned.

But my question is whats wrong in this case if we user varchar2 and store true or false on to varchars ??

Will this have any impact ??
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

A few issues spring to mind:
  • You are using more space by picking a larger data type
  • You complicate the issue because of case sensitivity
  • You introduce English terms into your data model (which may not be a problem if the data is English)
  • You introduce a more complex constraint to prevent bogus data appearing in your model
  • Ravi Kiran Va
    Ranch Hand

    Joined: Apr 18, 2009
    Posts: 2234

    Thats great answer from you , I didn't expect these many things needs to be taken care . Thanks paul once again .

    Martin Vajsar
    Sheriff

    Joined: Aug 22, 2010
    Posts: 3434
        
      47

    I prefer the numerical solution too. In any case I'd strongly suggest to use constraints to make sure no invalid values will ever be inserted into the table:

    I'm responding late, but I think this is quite important and can save lots of problems later.

    (Note that even in this case if someone will try to insert a real number (eg. double) between 0 and 1, Oracle will silently round the value and no error will be raised. Although even this kind of errors can be prevented with carefully crafted desing, I'm not that paranoid to try to catch these.)
     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: A question related to database table design
     
    Similar Threads
    Yes/No SQL Quieries
    Maintaining Session
    Mapping a oracle boolean to Java boolean data type in hibernate
    Mapping ENUM('0', '1') or CHAR(0) for Boolean
    Does (a=true) from if (a=true) evaluates to true ...