Data Modeling: use a series of BOOLs or 1 ENUM?

Say I have a table called "media" to represent audios, images, and videos. Furthermore, say I need to distinguish between the media types. Which is better:

TABLE: media
 - media_id
 - is_audio (BOOL)
 - is_image (BOOL)
 - is_video (BOOL)

OR

TABLE: media
 - media_id
 - type (ENUM: "audio", "image", "video")

What if there were 1000 media types? Do I split the model into 2 tables:

TABLE: media
 - media_id
 - media_type_id

TABLE开发者_JAVA技巧: media_type
 - media_type_id
 - name*

• NOTE: say there really is no need to know the "name" of the media type (e.g. you need to know it's an image, but don't care that it's a JPG).

---

Your third option – using a reference table – is probably the most widely supported, and it's what I regularly use.

With the third option, if the mediaTypeId column is foreign keyed to the MediaType table, you have guaranteed data integrity (unless you're using a flavor of SQL that doesn't enforce foreign keys, such as SQLite or MySQL with the MyISAM engine).

You don't get that with your first solution — you could potentially have a record in that table that has both is_audio and is_image set to 1. Additionally, as you add more media types, your table becomes wider, uglier, and clunkier.

Your second option (using enums) seems to be specific to MySQL (which is okay if you don't ever plan to use anything but MySQL). From its documentation page, it's actually a string type (which needlessly yet slightly takes up more space than an integer), and it seems very painful to add new options (see this question). Additionally, if you want other tables to reference media types, they all need their own enums.

I'd go with your third option, which is more portable, and it allows other tables to reference MediaType as well.