If I have multiple types of an object, when should object.type be a string and when should it be a foreign key?

Question

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!

Answer 1

if there's never more information to be stored against something, string is normally fine (though this is a non-transient value so it's not normal-form).

This seems like a good candidate for a table, however, so you might want to do more with categories so it should be a reference table, imo.

Answer 2

I would use fk. Less information duplicated.

EDIT: Betther solution: MySql code:

CREATE TABLE books
(
    id int AUTO_INCREMENT          not null,
    book_type enum('romance', 'fiction', 'mystery')     not null,
    ....
);

Answer 3

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.

Answer 4

In most cases the approach with a foreign key to a separate table is best - advantages:

• The separate table gives you an extensible way to validate the entry. Putting a hard-coded check constraint in the table definition then requires an ALTER TABLE to add a new type

• If you ever need to alter the type text for some reason (e.g. "romance"-> "women's fiction" for a lame example), you'll only have a lightweight update to the lookup table.

• You could conceivably have types for which there are no entries yet and the separate table lets you use an outer join to include the type in SQL result sets.

• From the interface perspective, a separate table lets you easily generate a drop-down list of types that doesn't require hard-coding in the UI.

As far as performance goes, with the proper index on the FK any RDBMS engine will perform well - joins are what an RDBMS is designed for.