Integer values for status fields

Question

Often I find myself creating 'status' fields for database tables. I set these up as TINYINT(1) as more than often I only need a handful of status values. I cross-reference these values to array-lookups in my code, an example is as follows:

0 - Pending
1 - Active
2 - Denied
3 - On Hold

This all works very well, except I'm now trying to create better database structures and realise that from a database point of view, these integer values don't actually mean anything.

Now a solution to this may be to create separate tables for statuses - but there could be several status columns across the database and to have separate tables for each status column seems a bit of overkill? (I'd like each status to start from zero - so having one status table for all statuses wouldn't be ideal for me).

Another option is to use the ENUM data type - but there are mixed opinions on this. I see many people not recommending to use ENUM fields.

So what would be the way to go? Do I absolutely need to be putting this data in to its own table?

No, you don't. Don't confuse data (as stored in a database) with information (data that has meaning applied). — BenM, Dec 27 '13 at 21:56
In my opinion, 1 table consisting of 4-5 columns an identity for uniqueness, an order starting at 0 increment by one for each "field" and then an active date and an inactive date to control when to display the values within the set for the field. when you need values you select from status where fieldname ='NameofYouField' so it's all maintained in once place. One additional field to indicate if it's maintainable (meaning it has special logic driven off the value so changing the ID, field or value would break the system. — xQbert, Dec 27 '13 at 22:01
possible duplicate of [Java: Enum vs. Int](http://stackoverflow.com/questions/9254637/java-enum-vs-int) — Bohemian, Dec 27 '13 at 22:28
`int` constants are flawed; see [this answer](http://stackoverflow.com/questions/9254637/java-enum-vs-int/9254703#9254703) for why. — Bohemian, Dec 27 '13 at 22:30
you can start the table from 1000000 not just 0, it comes by default as 0 but you can play around with it as much as you want! — mamdouh alramadan, Dec 28 '13 at 08:26

score 3 · Answer 1 · answered Dec 27 '13 at 21:56

I think the best approach is to have a single status table for each kind of status. For example, order_status ("placed", "paid", "processing", "completed") is qualitatively different from contact_status ("received", "replied", "resolved"), but the latter might work just as well for customer contacts as for supplier contacts.

This is probably already what you're doing — it's just that your "tables" are in-memory arrays rather than database tables.

score 0 · Answer 2 · answered Dec 27 '13 at 22:03

As I really agree with "ruakh" on creating another table structured as id statusName which is great. However, I would like to add that for such a table you can still use tinyint(1) for the id field. as tinyint accepts values from 0 to 127 which would cover all status cases you might need.

Branko Dimitrijevic · Answer 3 · 2013-12-27T22:32:12.790

Can you add (or remove) a status value without changing code?

If yes, then consider a separate lookup table for each status "type". You are already treating this data in a generic way in your code, so you should have a generic data structure for it.
I no, then keep the ENUM (or well-documented integer). You are treating each value in a special way, so there isn't much purpose in trying to generalize the data model.

(I'd like each status to start from zero - so having one status table for all statuses wouldn't be ideal for me

You should never mix several distinct sets of values within the same lookup table (regardless of your "zero issue"). Reasons:

A simple FOREIGN KEY alone won't be able to prevent referencing a value from the wrong set.
All values are forced into the same type, which may not always be desirable.

That's such a common anti-pattern that it even has a name: "one true lookup table".

Instead, keep each lookup "type" within a separate table. That way, FKs work predictably and you can tweak datatypes as necessary.

Integer values for status fields

3 Answers3