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