Using a table to provide enum values in MySQL??

Question

Is there a way to map one of the the columns contents of a MySQL table to an enum on another table in MySQL? I thought this would be a no brainer, but there doesn't seem to be any info that I can find on the subject.

Any advice or help on this matter would be cool and if it's not possible, does anyone know of an internal reason why it wouldn't be possible?

Best regards everyone :)

Gary

Answer 1

If it doesn't do it, don't do it

Surely you just want a table of possible keys and then a foreign key mapping to that.

If you want a table with possible enum values and restrictions, go for groupings via another table or a groupid in the same table (if group members are unique).

Smells like table-stink though JOIN wise. Maybe best doing this in a stored procedure or in the app code and mapping it to a native value?

Answer 2

The enum type is handy as a one-off, but it doesn't scale well to multiple tables and isn't standard SQL either. Best thing to do here is to use normal tables and relations:

1. Define a new table to hold the list of possible values; let's call it Master1
2. In the other two tables (let's call them Table1 and Table2), don't make the field an enum; just make it a normal field with a foreign key relation to Master1.

The foreign key relation will do the job of restricting to a list of possible values; and because foreign keys and relations are absolutely standard SQL, this approach will have other benefits - for example reporting tools can recognise the foreign key and understand how to use the related data.