Greetings all!
I've questions about the ENUM type. Reading the docs, it says that the size of an ENUM column is 1 or 2 bytes depending on the number of enumeration values (source:
http://dev.mysql.com/doc/mysql/en/Storage_requirements.html). So, from this information, is there any real difference with respect to storage requirements from using TINYINTs to represent boolean values (0 or 1) as opposed to ENUM columns (with values like 'TRUE','FALSE' or 'YES','NO'). The ENUM column is, perhaps, easier to understand at a glance than the numbers; especially for those who aren't programmers/administrators and might be handling data from the database. Further, the TINYINT column type is also marked as taking up 1 byte of information so it seems on the surface that in this case it's an equivalent use of space but one is easier for a larger group to understand.
Also, with regards again to the ENUM type: where are the values stored? If the column is only taking up 1 or 2 bytes, then I assume that the actually character values of the enumeration are not being stored in the table itself but rather somewhere else in the MySQL database.
What I'm trying to get at here is whether it is a more efficient use of space to create a list of codes and descriptions in one table and then use a second table that links to the first instead of ENUM columns. For example, accounts within my site can be new, confirmed, active, or inactive. I could have created a table called account_status_codes (or the like) giving a numerical id to each of those four status types. Then, other tables involving account status information could link information based on the numerical id of each code. However, I've -- for now -- simply created that information as a ENUM in one table since no other table needs to link the information, but I wonder about whether this is an efficient use of space on my server.
In this example, since no other table needs the information, I decided this was probably a good way to go. For information that would need to be used in multiple tables, I think linking a seperate table of codes would be more efficient for maintenance (change the code table, change the available codes for all fields rather than having to change the enumeration values for all fields individually). But, which is more efficient with regards to space?
Thanks!
David Goldfeder
Web and Database Specialist
University of Illinois, Urbana-Champaign
Department of Mechanical and Industrial Engineering