6

Say i have a datatype called "status" as a row in a mysql table. Status can only be a fixed number of strings, say "active", "inactive" and "pending". What datatype is best practice to use?

  • Make another table "statuses" and have an pointer id in my table?
  • Make a php array containing the different statuses and use my status-row as index for the array?
  • Simply letting status be a string containing the current status?
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
Viktor
  • 227
  • 2
  • 10
  • It sounds like you're trying to use an `ENUM`? Input/output is strings, but it can only be a specific set of values (and it saves it in a much better way than eg. `VARCHAR`s) – h2ooooooo Jan 17 '14 at 11:05

4 Answers4

4

Specifically for exactly that purpose is the ENUM type: http://dev.mysql.com/doc/refman/5.5/en/enum.html

CREATE TABLE ... (
    status ENUM('active', 'inactive', 'pending')
    ...
);
deceze
  • 510,633
  • 85
  • 743
  • 889
2

If the set of statuses is fixed at development time you definitely want to use ENUM. Another case is when possible statuses can be added in runtime. In this case you want to use separated table to store them and foreign key to check that all statuses are valid.

Using simple string for this is a kind of bad practice. E.g. just a minor misspelling in status can break everything related to the particular row.

vbo
  • 13,583
  • 1
  • 25
  • 33
1

Indexing is not sufficient for just 3 non-changable values .

For limited and fixed number of strings use SET type like this :

ALTER TABLE `table` ADD `status` SET( 'active', 'insactive', 'pending' ) NOT NULL

and for check the value of a field you can have access to them by their name

('active', 'insactive', 'pending') or even numbers ( 1 , 2 , 3 ) .

For example if the value of a filed is active :

if($value=="active") //returns TRUE
if($value== 1) //returns TRUE too
Alireza Fallah
  • 4,609
  • 3
  • 31
  • 57
  • [A `SET` can also include an empty value (which might not be wanted) - an `ENUM` cannot](http://stackoverflow.com/a/14798525/247893) – h2ooooooo Jan 17 '14 at 11:16
  • what about `NOT NULL` then ? – Samira Khorshidi Jan 17 '14 at 11:17
  • 1
    Do you know that your `status` field can contain something like 'active,inactive' if it's `SET`? It's strange to allow it. Why `SET` is better than `ENUM`? – vbo Jan 17 '14 at 11:41
-1

You may follow any of the three options you proposed plus you may use ENUM.

I'd avoid option number 1.

I'd go (but it's a matter of coding style and preference) with option nr. 3. Store the value as a string, with the fields with fixed size, like char with size of 16...

Paolo
  • 15,233
  • 27
  • 70
  • 91