0

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

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

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

Thanks

4
Contributors
3
Replies
26
Views
4 Years
Discussion Span
Last Post by drjohn
1

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

Edited by urtrivedi

1

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.