Enum vs Reference table vs Lookup class

Question

While I'm designing a MySQL database for a dating website, I have come with the doubt of how to store the referenced data. Currently the database has 33 tables and there are nearly 32 different fields who need to be referenced. We have to consider as well that many of these elements need to be translated.

After been reading several opinions, I have almost dismissed to use enum like:

CREATE TABLE profile (
  'user_id' INT NOT NULL,
 ...
  'relationship_status' ENUM('Single','Married') NOT NULL,
 ... 
);

And normally I would be using a reference table like:

CREATE TABLE profile (
  'user_id' INT NOT NULL,
 ...
  'relationship_status_id' INT NOT NULL, 
 ... 
);

CREATE TABLE relationship_status (
  'id' INT NOT NULL,
  'name' VARCHAR(45) NOT NULL,
  PRIMARY KEY ('id') 
);

But it might be over-killed to create 32 tables so I'm considering to code it in PHP like this:

class RelationshipStatusLookUp{
  const SINGLE = 1;
  const MARRIED = 2;

  public static function getLabel($status){
      if($status == self::SINGLE)
         return 'Single';
      if($status == self::MARRIED)
         return 'Married';
      return false;
  }
}

What do you think? Because I guess it could improve the performance of the queries and also make easier the development of the whole site.

Thanks.

the only thing id say, is if you decide to use that data outside of ur php code, option three wont be much help. Id stick with option two, and i really dont think performance is gonna take a hit at all, from having reference tables — Ascherer, Mar 15 '12 at 15:47
_"We have to consider as well that many of these elements need to be translated."_ Don't try to handle translation on the database level. You should handle this in your PHP. — augustknight, Mar 15 '12 at 15:52
Also, if you plan to implement an ORM ever, some of them, like Doctrine, dont even support enum — Ascherer, Mar 15 '12 at 16:04
http://stackoverflow.com/questions/336846/database-enums-pros-and-cons some good answers from a previous instance of this question — Mark Baker, Mar 15 '12 at 16:07

score 12 · Accepted Answer · answered Mar 15 '12 at 15:52

12

Definitely a good idea to steer clear of ENUM IMHO: why ENUM is evil. Technically a lookup table would be the preferred solution although for simple values a PHP class would work. You do need to be careful of this for the same reasons as ENUM; if the values in your set grow it could become difficult to maintain. (What about "co-habiting", "divorced", "civil partnership", "widowed" etc). It also not trivial to query for lists of values using PHP classes; it's possible using reflection but not as easy as a simple MySQL SELECT. This is probably one of those cases where I wouldn't worry about performance until it becomes a problem. Use the best solution for your code/application first, then optimise if you need to.

answered Mar 15 '12 at 15:52

liquorvicar

6,081
1
16
21

Yeah, I read it two days ago and it helped me to discard the enum, but I added it to the question as it was another option to consider about it. – Puigcerber Mar 15 '12 at 16:04
@Puigcerber I think ENUMs are worth considering. There *may* be some edge cases where they might be a good option. As long as you understand the drawbacks in using them... – liquorvicar Mar 16 '12 at 08:43
I was thinking maybe to use it with gender and some other fixed data, but I guess I'm gonna use reference tables for everything. If the reference is not enforced, would you allow NULL in the parent table or is it better to use 0 values in the referenced table? – Puigcerber Mar 16 '12 at 08:54

score 5 · Answer 2 · answered Mar 15 '12 at 15:58

enum fields present some issues:

Once they're set, they can't easily be changed
```
'relationship_status' ENUM('Single','Married') NOT NULL,
```
would need 'Civil Partnership' adding in this country nowadays
You can't easily create a dropdown list of options from the enum lists

However, data onthe database can be subjected to referential integrity constraints, so using a foreign key link against a reference table gives you that degree of validation without the constraints of an enum.

Maintaining the options in a class requires a code change for any new options that have to be added to the data, which may increase the work involved depending on your release procedures, and doesn't prevent bad data being inserted into the database.

Personally, I'd go for a reference table

score 2 · Answer 3 · answered Mar 15 '12 at 15:50

First off, you wouldn't need id and relationship_status_id in the Relationship_status table.

Personally, I would use an enum unless you need to associate more data than just the name of the person's relationship status (or if you foresee needing to expand on this in the future). It will be much easier when you're looking at the database to see what's what if it is in an easily readable language versus having to query against a second table.

When you are considering performance, sure it's faster to query a table by a unique ID but you have to track that relationship and you will always be joining multiple tables to get the same data. If the enum solution ends up being slower, I don't think it will be enough that the human brain will be able to perceive the difference even with large data sets.

I'm sorry, that `relationship_status_id` has been a mistake while copying and pasting. — Puigcerber, Mar 15 '12 at 15:54
joining on reference tables isnt really that difficult, and a database isnt really supposed to be in an easily readable language, its supposed to be structured in a way that provides the best structure, validity, and performance, of which enum provides none of these. — Ascherer, Mar 15 '12 at 16:03

Enum vs Reference table vs Lookup class

3 Answers3