Integer vs char for DB record property

Say I have a table with real estate listings. Every listing can be either 'For sale' or 'For rent'. Therefore, I can map 'For 开发者_运维问答sale' to 0, 'For rent' to 1 and store it as an INT in the database. However, it would be much more descriptive if I store it as 'sale' / 'rent' in a field of type CHAR. Or I can map 0 and 1 to two constants FOR_SALE and FOR_RENT in my program. Or use chars 'S' and 'R'. What are the best practices for storing such properties in a database with a condition that the total number of options for one such property is very small.

I'd store the attribute in the listing as an int and make it a foreign key to a lookup table where you can add your descriptions.

You should use a char(1) or an int (depending upon the number of options) and map the values to constant strings, this way you'll save space and the strings will be easily configurable in the future :)

PostgreSQL and MySQL support enumerated types, which is what you sound like you're looking for. Only problem with enumerated types is database portability. Oracle for example, does not have enumerated types so you have to use CHAR instead. So if you're set on PostgreSQL for example, there is no reason to not tap into its features. If you need database portability, using CHAR(1) or NUMBER(1) is most efficient.

Update: You can use a lookup table with a foreign key as other responses have mentioned but with boolean values that will not change, this would be introducing unnecessary complexity. Especially when you consider having to create additional classes for them in your ORM. If however, you are anticipating range of values for that column / variable to change, using lookup table is the best way to go.

I'd just use a char(1) for such a simple thing; I'd also slap a CHECK constraint on it (if available) to give some measure of sanity checking. Adding an extra table for something with only two values is a bit pointless even if it is pendantically correct. Also, an S or R in the column will help you when you're debugging or mucking around in the database by hand, a 1 or 6 will be pretty much meaningless.

Of course, if you have values and can't come up with sensible mnemonic characters to represent them, then the "int and an FK" approach makes more sense.

You can change from char(1) to "int and an FK" quite easily if it ever becomes necessary.