Should I create an enumeration or individual flags?

Question

I'm creating a new database table which will be used to store pictures of products. I'd like to identify the product "type" represented in the picture. Say we have 3 general categories for all the products:

• tables,
• chairs,
• and lamps.

Am I better off creating 3 boolean columns, is_table, is_chair, and is_lamp?

Or am I better off creating an enumeration, and then using a single column for that enum:

CREATE TYPE product_type AS ENUM ('table', 'chair', 'lamp' );

(Note that with the engineering product pictures we're storing, it is guaranteed a product/picture will never qualify as 2 types. It must always be 1 very specific type.)

Answer 1

You should normalize this and create a reference table, that way you can use a simple data type for the type indicator and also be future proof if you need to add types. It also makes for easier queries, plus this structure will maintain the constraint of each product only being of one type at a time.

so (assuming this is an Oracle db):

   Create table Product_types 
   (id int,
    name varchar2(100),
    constraint pk_product_types primary key  (id)
    );

create table products 
    (id int, 
     product_type int, 
     somecolumn varchar2(100),
     anothercolumn varchar2(100),
     somedatecolumn date,
     constraint fk_products_product_type foreign key (product_type) references product_types(id)
     );

Answer 2

I'd seriously recommend a separate table called product_types to store this info, something like:

products
--------
  ID
  Name
  Product_type_id (FK to product_types.id)

product_types
-------------
  ID
  Type_Name (such as "chair", "table", "lamp").

This is a solution that ensures products have a valid type and also scales well if you need to add more products in the future.

I would not use individual flags because you will have n flags per record, if you have n products. Having several flags might be OK if you actually plan to allow combinations of >1 flag being set, but it doesn't sound like that would make sense in this case.

I don't feel that an enumeration is a good fit for this either. I prefer to use enums for mostly static sets, such as a list of state/province/territory codes. If you know for a fact that your system will (probably!) never have more than these three types, then maybe an enum is OK, but I'd rather not use it here. Also, I'm not sure how well enums port to other databases, if such a need should ever arise...