CODEWITHSUNDEEP
mysqlsqldatabase-design
MAX POWER
MAX POWER

Reputation: 5448

Integer values for status fields

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?

Upvotes: 3

Views: 1163

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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.

Upvotes: 0

mamdouh alramadan
mamdouh alramadan

Reputation: 8528

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.

Upvotes: 0

ruakh
ruakh

Reputation: 183301

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.

Upvotes: 4

Related Questions