What is the advantage of using the ENUM type?

In general, few. First of all, the ENUM is used as a form of normalization. So using a similar mechanism might be useful.

If you have a list of data items that can be chosen exclusively, it may be highly interesting to have only one code in your table, one that identifies one. The description of it can be changed at some point, you may need other information associated with it, so if you know how to do normalization you should not put the description of the item, not only because it takes up more space.

Using this form is a good thing and should only be avoided if there is an important reason, if you need the maximum performance and know the risks and what you are doing.

What is being asked is to use ENUM to replace a related table. In general it only has disadvantages. Prefer the method above lookup tables .

• Reading items from ENUM is a more complicated process than it should and is slow. It is not a common data
• Changing ENUM itself requires moving the tables that use it, among other problems. It's quite true that a% should only be used if you know that it should never be changed .
• You can not add additional data beyond the description.
• Can not be internationalized.
• Is an element belonging to the table where it is used, that is, it is not a public member of the base and can not be reused, which practically kills the canonicalization advantage of the information.
• It is not so simple to use it correctly

I did not even talk about the portability problem because this is something that does not seem to be a problem in this question.

The advantage is that it takes up less space and you get the benefits of normalization, the separation of data details, but as stated before, you have better techniques.

Obviously I will not show you an example of using it. I'll just say to create tables that list this. There are two ways of using it. Or you create a table for each "enumeration" you need. Or, if you know you only need the description or other data fields that will be used in all the enumerations, then make an enumeration table with an extra column identifying which enumeration that entry refers to.

If you make multiple tables, essentially it will have a ENUM or code as the primary key and a column with the description. You can have others as needed.

The consumer tables for these enumerations will have a column with ID to relate to the respective enumeration. Eventually you can put a foreign key to facilitate the automatic relationship. Not everyone likes to do this. Obviously you need to ID s to get the description.

In some cases you can only have one number in the consumer tables and not have the table with the description, let the application handle it. It is not the most suitable from the point of view of the database, but it is still a viable option in many cases if you prefer to leave part of the decisions for the application.

If you use JOIN and describe it right there, possibly with VARCHAR to limit what can be used, is it wrong? No. Normalization is lost, it has some disadvantages. But it might be suitable for you and is usually more advantageous than CHECK , at least it does not offer more disadvantages than it.