CODEWITHSUNDEEP
phpmysql
Josh Mountain
Josh Mountain

Reputation: 1950

Should options be stored as text or integers in a MySQL DB?

I am creating a small database of names that has three columns: ID, Name, Status

The 'status' can be one of three things: waiting, approved, other

My question is, what is the best/most correct way to store the 'status' in the DB? Should it be stored as a varchar string or as a 1, 2, or 3 integer and later translate that to waiting/approved/other when reading from the DB? I hope that makes sense, thanks for any help.

Upvotes: 3

Views: 462

Answers (7)

Kermit
Kermit

Reputation: 34054

You should have a status table (referred to as a dictionary or lookup table) using a tinyint data type to reference that status. You would use a foreign key constraint. This way you maintain relational and domain integrity. It will allow you to add/change/remove status values without changing table structure.

status
=============
id  value
1   Waiting
2   Approved
3   Other
users
====================
id  name   status_id
1   Bobby  3

Upvotes: 6

Lars
Lars

Reputation: 600

Create another table called

statuses with 2 fields id INT Primary Auto Increment name varchar(30).

Add your statuses in the user table by integer and reference them with a JOIN.

You can also change status in the users table to status_id it would make more sense.

IMO this is the most appropriate way in

Upvotes: 1

JvdBerg
JvdBerg

Reputation: 21856

I think they should be stored as what they are: a enum!

Example:

status ENUM('waiting', 'approved', 'other') DEFAULT 'other'

Upvotes: 0

FThompson
FThompson

Reputation: 28687

An integer field uses less memory space than a varchar field, so storing your 'status' option as a number will reduce memory usage marginally.

I recommend storing 'status' in an integer and translating it to the corresponding value externally when necessary.

Upvotes: 0

a coder
a coder

Reputation: 7649

Use numerics with a separate lookup table.

Upvotes: 0

BryanH
BryanH

Reputation: 6062

Generally speaking, store those items as text, unless:

  • You will be searching for them: character searches take longer and are more expensive than numeric (integer) searches
  • You will need to do 'math' (summation, averages, standard deviations, etc) on them: casting/converting to a number, performing the calculation and then doing something with the results is very process-intensive)

Upvotes: 0

Alain Collins
Alain Collins

Reputation: 16362

Integers are smaller. Integers can be translated by your app into multiple languages, etc.

Integers good.

Check out ENUM. If not, make sure you're using the right sized integer.

Upvotes: 0

Related Questions