File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

A question related to database table design

 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The normal pattern (if your database doesn't support a boolean type) is to use integers.
 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Posts: 2234
    Eclipse IDE Firefox Browser Redhat
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thats great answer from you , I didn't expect these many things needs to be taken care . Thanks paul once again .

     
    Martin Vajsar
    Sheriff
    Pie
    Posts: 3747
    62
    Chrome Netbeans IDE Oracle
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.)
     
    I agree. Here's the link: http://aspose.com/file-tools
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic