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?


Re: Best datatype to store status in mysql? 80 80

the best solution would be to create a separate table for the status

Re: Best datatype to store status in mysql? 80 80

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

Re: Best datatype to store status in mysql? 80 80

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.