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?


Recommended Answers

All 3 Replies

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

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

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.