Status field contains values Open,Awaiting,Approved,Rejected. To store a value what is the best datatype in mysql? Is it TINYINT OR VARCHAR? TINYINT occupies less memory if we store numbers instead of strings ( 1 => Open, 2 => Awaiting ...). Is it cause any performence issue when using joins?
I want to create a table to store status values like this

------    ------
Open        1
Awaiting    2
Approved    3
Rejected    4

Is it good idea? or proceeding with varchar is good idea?


4 Years
Discussion Span
Last Post by drjohn

In my view, if possible status list is not going to change ever, then you can use enum type

Edited by urtrivedi


You have no need for the value column.
Just a simple one column table, where status is a varchar. No need for joins, use it as a lookup table in queries to populate a drop down options box in a fomr a user completes.

It's a common beginner's mistake to add a numerical id value to a single column table under the mistaken belief that all tables need a numeric id. As there will NEVER be two different statuses with the status Open or Awaiting, etc, the status forms what is called a natural key. It uniquely defines itself. Using a number to reperesent a word means that the value then has to be "translated" into th eword by carrying out a totally unnecessary join to get at the real word.

You would only need a numerical id if there were other attributes associated with a row in the table, such as assigned, date, owner, and so on.

Edited by drjohn

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.