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 store1
if it's an income,0
if it's an expense,null
if not known. - Have two columns,
Income
andExpense
, setting their values to1
or0
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) fornull
(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