MYSQL|Datatype for states

Question

I need to figure out which datatype to use for states.

Should it be SET or VARCHAR or anything else?

CREATE TABLE actors(
    state SET('USA','Germany','....)
    )

alternatively

CREATE TABLE actors(
state VARCHAR(30)
)

Answer 1

Assuming there's going to be tens or over hundred of the countries, it's best to use separate table.

CREATE TABLE states(
  state_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30)
);

It's also recommended to use foreign key on the state_id so if you want to delete a state from your database, it wouldn't break other data depending on it.

If each actor is going to be assigned only to one state (1:1), you can use column in the actors table.

CREATE TABLE actors(
   actor_id INT ...,
   state_id INT,
)

Or if each actor can be assigned to more states (1:N), use another table for these relations:

CREATE TABLE actors(
   actor_id INT ...,
)
CREATE TABLE actors_to_states(
   actor_id INT,
   state_id INT
)