2

This is a direct follower to this question. An answer given to that questions says (my understanding), that nowadays using string-based enums instead of integers isn't a much problem, because hosting costs and general DB costs are today much, much lower, so we can afford for some "database size waste" (generalization).

I'm interested, at which point this can turn itself around and even in nowadays become a real problem?

Suppose I have a table with n columns, out of which four columns uses string-based enums instead of integers. Assume, that each enum is 8 characters long, if that matters (probably yes -- string column size). Now, suppose, that I'm filling this table with records. When I may start thinking, that using enums was a wrong decision?

At 100k records? At 1M records or maybe not before reaching 1G of records?

Is there an easy way for me to determine:

  • what would be size difference, if my database with n records would be using integers instead of strings?

  • what general database size could become a real problem for my hosting?

I've heard that on simple, shared hosting a 1 GB database size becomes a problem and bigger databases are not allowed / not much liked by hosting's admins. But, since I'm a complete database newbie, I don't know, if that is true? And how does it becomes, when we talk about VPSs or even dedicated hosting?

Community
  • 1
trejder
  • 2,386
  • 3
  • 19
  • 39
  • 5
    Terminology note: in your question, you should be using 'string' rather than 'enum'. `enum` native data types (i.e. defined by system) are generally stored as an integers, not strings. `enum` abstract types are those with a finite, non-extendable set of simple values; these abstract types aren't strings or integers. If there isn't a native type that corresponds to the abstract notion of an `enum`, you can implement them using strings or integers (or any primitive value type with more distinct values than the enum), which is where these questions of picking a representation arise. – outis Jun 20 '15 at 20:46
  • 1
    I took the freedom to edit the question the way it was probably meant. But if you were not talking of "strings as enums in general", but of the "ENUM" type of MySQL, the the whole question is based on a false assumption (because those are not stored as strings internally, but as integers). If that is the case, you might consider to delete it. – Doc Brown Jun 21 '15 at 07:51
  • I, of course, made a pure mistake and false base assumption, due to my lack on knowledge. Therefore, I'm totally and completely grateful to all of you for all the fixes, that made this question "valid". Free edits is one of the most fabulous feature of SE. Thank you! – trejder Jun 21 '15 at 07:53

3 Answers3

5

The problem is not space. Because space is cheap.

It really takes a good book to explain what is wrong with repeating texts in a column, but it's a bad idea. You will get it when you read about database normalization. Database normalization is what strong typing is for programming languages. You can live without, especially for very small projects, but it's way easier to end up with a complete mess. So for starters, do it the traditional way.

Basically, you should have a lookup table with all your values and the primary key of said table should be in the table where you would have entered the text. As a foreign key relationship.

This means the database has to join those two tables. But that's a databases job. Only if you think you have such a heavy load that you need to optimize (hint: as long as you are only talking about millions of records and gigabytes, you don't), you should flatten your table relationships.

Again, read a good book about database design first, it will save you a lot of headaches.

nvoigt
  • 7,271
  • 3
  • 22
  • 26
  • And if there's a need to flatten, perhaps the database can do it for you, without incurring the penalties of saving things denormalized: Look for materialized views. – Deduplicator Jun 20 '15 at 22:33
3

Actually, internally the database is not going to store your string representation of your enum in every record. It is effectively making a lookup table of it's own and translating the stored integer value to string (and back) when you interact with the database...

From: http://dev.mysql.com/doc/refman/5.0/en/enum.html

If you retrieve an ENUM value in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an ENUM column like this:

mysql> SELECT enum_col+0 FROM tbl_name;

Bill Heller
  • 341
  • 1
  • 3
1

Enums are not only faster to insert, delete, update and read. They also take up less disk space, and most importantly: they ensure that you can only store a limited set of values.

If you create a field, and you only want to allow values "male" or "female", then an enum is handy to make sure that nobody stores "man".

Text fields are just a bad solution to store a limited set of values, for similar reasons that you shouldn't use text fields to store numbers or dates, regardless of the performance implications.

Wouter van Nifterick
  • 493
  • 3
  • 11