CODEWITHSUNDEEP
mysqlenumsdatabase-normalization
Gino Sullivan
Gino Sullivan

Reputation: 2213

MySQL Database normalization

Should I implement a read database normalization (using join tables) or should I use the ENUM type for static or dynamic data?

For example:

I have a table USER with a user_status. Should I create a table a status table or I create a ENUM list with the statuses?

Thanks G

Upvotes: 22

Views: 1307

Answers (3)

symcbean
symcbean

Reputation: 48387

IMHO, the enum extension makes it much easier to embed semantics into a table and also improves efficiency by:

  1. decreasing the number of joins required for a query
  2. reducing the number of open tables in the DBMS

The only downsides I am aware of is

  1. the ENUM type is not implemented by other DBMS
  2. if you choose to add additional values to the ENUM set at a later date, you are applying a DDL update - which may take a long time with a very large table

HTH

C.

Upvotes: 26

Guilhem Hoffmann
Guilhem Hoffmann

Reputation: 962

An other stuff to be considered...

An enum could only be updated thru a modification of the database structure elsewhere a linked table permits dynamic creation of record.

Upvotes: 1

ravnur
ravnur

Reputation: 2852

It depends on architecture and many other factors.

For example, you do not allow reading/writing data except using stored procedures. In this case you can feel free use "tinyint" datatype. If you allow reading/writing with direct queries it should be better to use constraint i.e. ENUM to avoid improper statuses (if UI or back-end can put this "wrong" status of course) .

On the other hand (and it's possible) there can be changes in data flow and maybe you will need to add new statuses. In this case you you will need: 1) do nothing if you have static datatype; 2) do alter if you have ENUM.

So... my answer is: it depends on your application and your requirements.

Upvotes: 0

Related Questions