Say I've got a table with property entries. Every listing could be either 'For sale' or 'For rent'. Therefore, I'm able to map 'For sale' to , 'For rent' to at least one and store it as being an INT within the database. However, it might be a lot more descriptive basically store it as being 'sale' / 'rent' inside a area of type CHAR. Or I'm able to map and 1 to 2 constants FOR_Purchase as well as for_RENT during my program. Or use chars 'S' and 'R'. Do you know the guidelines for storing such qualities inside a database with a disorder that the entire quantity of choices for one particular rentals are really small.

I'd keep attribute within the listing being an int and turn it into a foreign answer to a research table where one can add your explanations.

alt text

You need to use a char(1) or perhaps an int (based upon the amount of options) and map the values to constant strings, by doing this you will save space and also the strings is going to be easily configurable later on :)

PostgreSQL and MySQL support enumerated types, that is that which you seem like you are searching for. Only trouble with enumerated types is database portability. Oracle for instance, doesn't have enumerated types so make use of CHAR rather. Therefore if you are set on PostgreSQL for instance, there's pointless not to make use of onpar gps. If you want database portability, using CHAR(1) or NUMBER(1) is most effective.

Update: Use a research table having a foreign key as other reactions have pointed out however with boolean values that won't change, this is presenting unnecessary complexity. Especially considering needing to create additional courses of instruction for them inside your ORM. You can definitely, you're anticipating selection of values for your column / variable to alter, using research table is the easiest method to go.

I'd only use a char(1) for this type of simple factor I'd also slap a CHECK constraint onto it (if available) to provide a degree of of sanity checking. Adding an additional table for something with only two values is a little pointless even when it's pendantically correct. Also, an S or R within the column can help you when you are debugging or mucking around within the database manually, a 1 or 6 is going to be virtually meaningless.

Obviously, for those who have values and should not develop sensible mnemonic figures to represent them, then your "int as well as an FK" approach will work better.

You are able to vary from char(1) to "int as well as an FK" very easily whether it ever is needed.