• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

A question related to database table design

 
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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


 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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 .

     
    Sheriff
    Posts: 3837
    66
    Netbeans IDE Oracle Firefox Browser
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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.)
     
    Don't get me started about those stupid light bulbs.
    reply
      Bookmark Topic Watch Topic
    • New Topic