Is this an appropriate use of a MySQL enumeration data type?_问答_开发者

I have recently started doing freelance PHP + MySQL development in my free time, to supplement my income from a full-time job where I write C#/SQL Server code. One of the big database-related differences I've noticed is that MySQL has an enum datatype, whereas SQL Server does not.

When I noticed the enum datatype, I immediately decided to flatten my data model in favor of having a big table that makes use of enumerations rather than many smaller tables for discrete entities and one big "bridge" sort of table.

The website I'm currently working on is for a record label. I only have one table to store the releases for the label, the "releases" table. I have used enumerations everywhere I would normally use a foreign key to a separate table--Artist name, Label name, and several others. The user has the ability to edit these enumeration columns through the backend. The major advantage I see for enumerations over using a text field for this is that artist names will be reused, which should improve data integrity. I also see an advantage in having fewer tables in the database.

Incidentally, I do still have one additional table and a bridge table--there is a "Tags" feature to add tags to a particular release, and since this is a many-to-many relationship, I feel a discrete tag table and a bridge table to join tags to releases is appropriate

Having never encountered an ENUM datatype in a database before, I wonder if I am making wise use of this feature, or if there are problems I haven't foreseen that might come back to bite me as a result of this data architecture. Experienced MySQL'ers, what开发者_运维百科 do you think?

In short, this is not a good design. Foreign keys have a purpose.

From the documentation for the ENUM type:

An enumeration can have a maximum of 65,535 elements.

Your design will not allow you to store more than 65k distinct artist names.

Have you considered what happens when you add a new artist name? I assume you are running an ALTER TABLE to add new enum types? According to a similar SO question this is a very expensive operation. Contrast this with the cost of simply adding another row to the artist table.

What happens if you have more than one table that needs to refer to an artist/artist's name? How do you re-use enum values across tables?

There are many other problems with this approach as well. I think that simplifying your database design like this does you a real disservice (foreign keys or having multiple tables are not a bad thing!).

I'm going to be honest - I stopped when I read...

I have used enumerations everywhere I would normally use a foreign key to a separate table--Artist name, Label name, and several others.

If I understand correctly, that means there is an enumeration of all artists. But that enumeration of artists is definitely going to be a point of variation: there will be more artists. I sincerely doubt the record label never plans on increasing or changing the list of artists ;)

As such, in my opinion, that is an incorrect use of an enumeration.

I also don't think it's appropriate to perform an ALTER TABLE for what is inevitably a rather mundane use case. (Create/Read/Update/Destroy artist) I have no numbers to back up that opinion.

You have to look at it as a question of what information is an entity or an attribute of an entity: for a record label, artists are entities, but media types may not be. Artists have lots of information associated with them (name, genre, awards, web site url, seniority...) which suggests they are an entity, not an attribute of another entity such as Release. Also, Artists are Created/Read/Updated and Destroyed as part of regular everyday use of he system, further suggesting they are entities.

Entities tend to get their own table. Now, when you look at the Media Type of these Releases, you have to ask yourself whether Media Type has any other information... if it's anything more than Name you have a new Entity. For example, if your system has to keep track of whether a media type is obsolete, now there are 2 attributes for Media Type (name, is obsolete) and it should be a separate entity. If the Medai Types only have a Name within the scope of what you're building, then it's an attribute of another entity and should only be a column, not a table. At that point I would consider using an enumeration.

I dont think you can use enumerations in fields like artists. Its like you are restricting your application from growing. It will be really hard to maintain the column. Using ENUM is not a problem its own. But will be an issue in the following situations

When you need to add additional options to the enum colum. If you are table contains lots of data, it will take good time to rebuild your table when adding an additional option
When you need to port the the database to another technology (enum is not available in all database products, for eg MSSQL)