So I'm creating an EER model diagram using MySQL Workbench.
I have a table task_types with only one column task_type_id, where I store a list of different types type A, type B,... That may be updated in the future.
This table is related to other tables, e.g. answers, evaluations.
The idea is: when I add a new type to task_types, this type becomes instantly available to these other tables.
The problem comes when deciding which data type should task_type_id be.
Should it be a TINYINT with auto increment?
Should it be an ENUM("type_A", "type_B", ...) that I can update when I decide to add a new type?
Should it be a VARCHAR(n) so I can store "type A" or "math test" instead of a numeric value?
I liked the idea of an ENUM type because it allowed me to describe the set of allowed values pre defined on the schema, though I still have to fill task_types with these values for the implementation to work.
The problem is: when I express the relation between these tables (entities) a new column is automatically created with the ENUM type in each of the related tables. If I want to redefine the ENUM type I have to do it not only in task_types but also in each related table. That makes no sense.
I think I may be having a groking problem with my approach. What am I doing wrong?
