Schema for storing "binary" values, such as Male/Female, in a database

Posted by latentflip on Stack Overflow See other posts from Stack Overflow or by latentflip
Published on 2010-04-27T20:28:22Z Indexed on 2010/04/27 20:43 UTC
Read the original article Hit count: 228

Intro

I am trying to decide how best to set up my database schema for a (Rails) model. I have a model related to money which indicates whether the value is an income (positive cash value) or an expense (negative cash value).

I would like separate column(s) to indicate whether it is an income or an expense, rather than relying on whether the value stored is positive or negative.


Question:

How would you store these values, and why?

  • Have a single column, say Income, and store 1 if it's an income, 0 if it's an expense, null if not known.
  • Have two columns, Income and Expense, setting their values to 1 or 0 as appropriate.
  • Something else?

I figure the question is similar to storing a person's gender in a database (ignoring aliens/transgender/etc) hence my title.


My thoughts so far

  • Lookup might be easier with a single column, but there is a risk of mistaking 0 (false, expense) for null (unknown).
  • Having seperate columns might be more difficult to maintain (what happens if we end up with a 1 in both columns?

Maybe it's not that big a deal which way I go, but it would be great to have any concerns/thoughts raised before I get too far down the line and have to change my code-base because I missed something that should have been obvious!

Thanks, Philip

© Stack Overflow or respective owner

Related posts about database-design

Related posts about schema