To separate to a different table or use enum type?

Question

I'm working with an online shop, currently products are either available or not, and enabled or not, by an int field on the products table.

They want me to add more to this, e.g. "in stock with supplier", "out of stock, ships within x days" etc. I'm thinking of just ditching the flags and creating a status field of enum type, with 'available', 'out of stock', 'coming soon', discontinued' etc.

What's the best way to do this? Add the enum type as a new column on products?

I got told I should add a separate table called Status and have a foreign key reference to it in the products table so it'll be easier to change in the future. I'm not sure how this is the case, as the enum type seems easy enough to change. You can add to it, remove from it and rename items.

Answer 1

a table with an FK will take a bit less space on the hard disk but I prefer using an Enum type. The difference is generally small and there is a performance benefit of not over normalizing.

Answer 2

If you anticipate ever changing the enum, you should probably use a separate table. It's less expensive to INSERT or UPDATE than it is to ALTER.

Also, consider how you would determine acceptable values for that field. If it's an enum, you have to hard code the options or inspect the database schema. If it's in a separate table, you can SELECT to populate a list of options.