Say i have a datatype called "status" as a row in a mysql table. Status can only be a fixed number of strings, say "active", "inactive" and "pending". What datatype is best practice to use?
If the set of statuses is fixed at development time you definitely want to use ENUM. Another case is when possible statuses can be added in runtime. In this case you want to use separated table to store them and foreign key to check that all statuses are valid.
Using simple string for this is a kind of bad practice. E.g. just a minor misspelling in status can break everything related to the particular row.