I've been browsing SQL dumps of some famous CMSes, including Drupal 7, WordPress (some quite very old version), and some custom application based on Python.
All of these dumps contained data with string flags instead of integer ones. For example, a post's status was represented as published, closed, or inherit rather than 1, 2, or 3.
I have quite limited experience in design of databases and I have never went past simple SQLs, but I was always taught that I should use numeric/integer flags for data like this. It's obvious that tinyint consumes much less space in a database than, for example, varchar(9).
So what am I missing? Isn't this a waste of data storage and a data redundancy? Wouldn't browsing, searching and indexing be a bit faster if these columns used integers instead of strings?
Best Answer
Yes, storing strings instead of numbers can use more space. The reason that high-profile pltforms are doing it anyway is that they think the benefits of that solution are greater than the cost.
What are the benefits? You can easily read a database dump and understand what it's about without memorizing the enum tables, and even semi-official GUIs might simply use the values themeselves rather than transform the record they get. (This is a basic form of disk space/processing time tradeoff.)
What about the cost? Data storage capacity hasn't been the bottleneck in CMS for a long time, since disks have gotten so large and so cheap. Programmer time, on the other hand, usually becomes more expensive - so anything that trades development effort for disk space is also a good thing, from a business perspective.