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

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

How would you store these values, and why?

I would store them as a single column. Despite your desire to separate the data into multiple columns, anyone who understands accounting or bookkeeping will know that the dollar value of a transaction is one thing, not two separate things based on whether it's income or expense (or asset, liablity, equity and so forth).

As someone who's actually written fully balanced double-entry accounting applications and less formal budgeting applications, I suggest you rethink your decision. It will make future work on this endeavour a lot easier.

I'm sorry, that's probably not what you want to hear and may well result in ngative rep for me but I can't, in all honesty, let this go without telling you what a mistake it will be.

Your "thoughts so far" are an indication of the problems already appearing.

1/ "Having seperate columns might be more difficult to maintain (what happens if we end up with a 1 in both columns?" - well, this shouldn't happen. Data is supposed to be internally consistent to the data model. You would be best advised preventing it with an insert/update trigger or, say, a single column that didn't allow it to happen :-)

2/ "Lookup might be easier with a single column, but there is a risk of mistaking 0 (false, expense) for null (unknown)." - no mistake possible if the sign is stored with the magnitude of the value. And the whole idea of not knowing whether an item is expense or income is abhorrent to accountants. That knowledge exists when the transaction is created, it's not something that is nebulous until some point after a transaction happens.

Sometimes I use a character. For example, I have a column gender in my database that stores m or f.

And I usually choose to have just one column.

I would typically implement a flag as an nchar(1) and use some meaningful abbreviations. I think that's the easiest thing to work with. You could use 'I' for income and 'E' for expense, for example.

That said, I don't think that's a good way to do this system.

I would probably put incomes and expenses in separate tables, since they appear to be different sorts of things. The only advantages I can think of for putting them in the same table are lost once the meanings are differentiated by flags rather than postitive and negative values.