Let's say I have books which can be romance, fiction, or mystery. I have 2 realistic options to store this data. One is to have a type column in my books table that is a string with the value "romance", "fiction", or "mystery". The other is to create a book_types table and store the types in there. Then my books would have a type_id foreign key referencing the book_types table.
My question is how do I pick which is best? I have seen the string method used in the Restful authentication Rails plugin which contains information about the users state - 'inactive','active','pending'...
Is there a performance hit for using the lookup table method considering I will be querying for this information all the time?
Thanks!
The foreign key approach will perform better. String comparison will slow things down. It's much faster to compare numbers.
If you want to speed up queries even more, add an index on the column you are using to reference the foreign key. Unlike for primary keys, indexes are not automatically created for foreign keys.