CODEWITHSUNDEEP
phpmysqlsqlenums
lightning_missile
lightning_missile

Reputation: 2992

Best practices for putting values inside sql enums

I have two enum columns in my mysql table:

gender enum('male', 'female') not null,
user_type enum('hr', 'manager', 'qa') not null

One of my coworkers insisted that it is not a good idea to use lower case in all enum values because it will not look good in pages. For example,

// profile.php
User Type: HR(stands for human resources)
Gender: male

should be:

// profile.php
User Type: HR
Gender: Male

My primary reason for ignoring cases is I will be comparing values on these columns lots of times. For example:

if (user_type == "hr")

it would be very annoying if I have errors just for case mismatch. And what about the gender column? Is it really fine to upper case it?

I suppose the UI side could just capitalize it like:

<?php echo strtoupper($userType) ?>

I don't have much knolege in sql so I'd like to ask: Is there best practices regarding case conventions on enum values? Note I'm not talking about naming enum columns.

Upvotes: 1

Views: 4088

Answers (2)

Alberto Martinez
Alberto Martinez

Reputation: 2670

In my experience, in all the languages that I used which have the concept of enums, both in the documentation and in the enums defined in the libraries provided by the language the enums are not written in uppercase or lowercase, but instead they use some variation of camel case, like Small, Medium, Big or szSmall, szMedium, szBig. Some examples:

Of course in all of these examples the enums values are not strings and they have a name and a value so you don't have a problem with comparisons nor with the output (because you would output its value, not its name).

Since in MySQL the value is also the name if you are using the values of the enum for any output you should use the case that have more sense in that output (the same that you would use if the column were a regular CHAR column), for example 'HR','Management','QA'. As noted in the comments the default collations should be already case insensitive so you don't have a problem in comparisons or joins.

In PHP you can use strcasecmp() or if you don't like it for any reason strtolower()/strtoupper():

if (strcasecmp(user_type,"hr")==0) // best options IMO
if (strcasecmp(user_type,"HR")==0) // best options IMO
if (strtolower(user_type)=="hr")
if (strtoupper(user_type)=="HR")

That is not that I'm against of using always a consistent case, in fact, I always use it and I worked a lot of time with case insensitive languages where it's not needed. But the priority is that the program provide the desired results, because you are writing code not (only) for fun but to achieve something, if the proper way of naming departments is 'HR','Management','QA' you can't use 'hr','management','qa' because it looks prettier in your code.

That's said, if the enums are used only internally, for example because you use them only as id's and the descriptions are stored in a Descriptions table, then use the case that is best suited for you and your coworkers.

Upvotes: 1

Ravi
Ravi

Reputation: 31397

If you are asking about Best Practices, then YES. You should use ENUM values in upper case. Because, they are constants.

And, AFAIK, in all programming languages constants declared as UPPERCASE.

Upvotes: 4

Related Questions