CODEWITHSUNDEEP
mysqldatabase
Naresh Ramoliya
Naresh Ramoliya

Reputation: 828

Alternative to ENUM Datatype in MySQL

I have used the ENUM datatype for fields in table but its values not fixed like may be changed in future. So what is the alternate solution to ENUM fields to be implemented.

Either take reference table or make column varchar or integer?

Upvotes: 3

Views: 4703

Answers (2)

Álvaro González
Álvaro González

Reputation: 146430

I think you answered your own question when you said that values might change in the future: you have to switch to an approach where data is stored once and in a central location (versus being scattered across your entire database); in other words, a master table.

An added benefit is that changes to values do not require DDL commands (ALTER TABLE) but simple DML ones (INSERT INTO, UPDATE). That allows to even write a simple administration.

For example:

CREATE TABLE car (
    car_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    plate VARCHAR(20) NOT NULL,
    color ENUM('White', 'Black', 'Red', 'Blue'),
    PRIMARY KEY (car_id),
);

... gets replaced by:

CREATE TABLE car (
    car_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    plate VARCHAR(20) NOT NULL,
    color_id INT(10),
    PRIMARY KEY (car_id),
    CONSTRAINT car_fk1 FOREIGN KEY (color_id) REFERENCES color (color_id)
);

CREATE TABLE color (
    color_id  INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (color_id),
    UNIQUE INDEX name (name)
);

Needless to say, you can reuse your colour definitions in any other entity that has colours.

Upvotes: 6

obe
obe

Reputation: 7808

I like using varchar, and then declare constants in the code that map to the proper values. I often also list the possible values and their meaning in the comment for the column.

Using varchar is less efficient than using tinyint (like Aju John suggested in his comment), but unless I'm dealing with a really performance-critical or a huge table - I find it easier to deal with. It allows me to understand the actual values when manually SELECTing data from the table and when debugging code (i.e. without remembering what each number means if I were to use an integer column), and in most cases this trade-off between efficiency and convenience is well worth it.

Upvotes: 0

Related Questions