Melvin Koopmans Melvin Koopmans - 9 months ago 22
MySQL Question

Strings vs integers for storing data

What would be considered the best practise when it comes to storing datatypes to the database?

For instance, you have a support ticket feature in which a ticket can have the following statuses:

open
,
closed
,
review
.

Would you store it as a string?

+----+----------------+--------+
| id | ticket | status |
+----+----------------+--------+
| 1 | example ticket | open |
| 2 | example ticket | closed |
+----+----------------+--------+


Store the value as an integer, so:
1
=>
open
,
2
=>
closed
and
3
=>
review
or as a string.

+----+--------+
| id | status |
+----+--------+
| 1 | 1 |
| 2 | 2 |
+----+--------+


If you would save it as an integer, would you create an extra table to resolve the name of that status (lets say
support_ticket_states
table)?

+----+----------------+--------+
| id | ticket | status |
+----+----------------+--------+
| 1 | example ticket | 1 |
| 2 | example ticket | 2 |
+----+----------------+--------+


Or would you do that on the client side, for example:

if ($ticket->status == 1) {
echo 'Open';
}


What would be considered the best option?

Answer Source

It depends on personal preference.

  • firstname: Would you store "Daniel" as string, or map every user to firstname_id=1? guess no...
  • status: new/open, only 2 options, but here I would use a mapping.
  • hourly/daily 2 options as well, but here i would take a string-type.

My preference: If there can be information associated with the entry - use a mapping table. If it is guaranteed to stay a "single-string", store it as string.

Picking the examples above:

  • The firstname always remains a single "firstname", altering a name should not affect other users. -> String
  • status could be extended with more information such as "general-time-to-response", "description", "team-lead" etc... -> Mapping
  • hourly/daily: There is nothing more to say, it's either or. -> String

if ($ticket->status == 1) {
    echo 'Open';
}

this depends on how you do your localization, if any. There is no "right" and "wrong", just "working" and "not working". :-)

I would keep it as open, closed in the database, and then - when it comesdown to localization - translate that entry properly using "code", not database-queries. (Loacalization stuff can be easily cached and preloaded, no need for over-engineered queries, cause you didn't.):

echo getLocalizedContent($ticket->status, $user->getLanguage());

(Language information can be stored along with the session, so no need to pass as parameter):

echo getLocalizedContent($ticket->status);