Should I use ENUM & SET datatypes in MySQL DB?

Question

MySQL

Hello, recently I started developing one project (personal), where you need to create user management functionality, and everything related to them. The project is made on Symfony 2 with all its standard components (including DoctrineORM). Each user must have their own profile, which they can edit. Using DoctrineORM in SF2, I created two entities between which a 1:1 relationship is established - Account and Profile. The account stores all the data necessary for authentication and authorization, and the profile will store all the information that the user will manipulate with. For example, in the profile entity, you need to save the user's gender and marital status, and, for example, his knowledge of foreign languages. In the first case, at first glance, the ENUM data type would not be bad,
Of course, I wondered if this would be the right decision ?! For example, DoctrineORM itself does not have such a built-in type, and you need to make it yourself Cookbook - Mysql Enums . This is not even the trouble, but the fact that its use is considered bad practice, this is even written "in the link itself". I read a lot of articles where knowledgeable people list the shortcomings and all the problems that will have to be faced. And I’m generally silent about the SET data type, because I didn’t find any good information, so I hope to hear reasonable answers from you. So, is it worth using them in my case? Or maybe there is some alternative option, I would like to hear how to implement it on DoctrineORM itself, for example, save it somehow through data types (array, simple array, json_array, object),

Answer 1

I did not use Set, but I use Enum successfully. It is used very simply. Moreover, I create a separate class for each enum in the project, which gives some advantages, for example, easy access to an array of valid values.
I create a DBAL folder in the project, and create the following classes there:

<?php

namespace AppBundle\DBAL;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

abstract class EnumType extends Type
{
    protected $name;

    public static $VALUES = array();

    public static function getValues()
    {
        return array_combine(static::$VALUES, static::$VALUES);
    }

    public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        $values = array_map(function($val) { return "'".$val."'"; }, static::$VALUES);

        return "ENUM(".implode(", ", $values).") COMMENT '(DC2Type:".$this->name.")'";
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return $value;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if (!in_array($value, static::$VALUES)) {
            throw new \InvalidArgumentException("Invalid value '$value' for enum '$this->name'.");
        }
        return $value;
    }

    public function getName()
    {
        return $this->name;
    }
}

This class is the base class for custom Enum classes, here is an example of one such:

<?php

namespace AppBundle\DBAL;

class GenderType extends EnumType
{
    protected $name = 'gender';
    public static $VALUES = array('male', 'female');
}

Now we need to teach the doctrine to use them:

doctrine:
    dbal:
        # ...
        types:
            gender: AppBundle\DBAL\GenderType
        mapping_types:
            enum: string
            gender: gender

Can now be used in entity descriptions:

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;

/**
 * @ORM\Entity
 */
class Person
{
    // other fields

    /**
     * @var string
     *
     * @ORM\Column(type="gender")
     */
    protected $gender;

    // setters & getters
}

And anywhere in the code you can get a list of available values:

foreach (GenderType::$VALUES as $gender) {
    echo $gender;
}

As for the SET type, I think it is necessary to write a similar class that can translate from a php value into an sql format, which will be correctly stored in the field. But for me, it's better for languages ​​to use simple_array, and not bother with them.

Answer 2

I use this solution, very great https://github.com/fre5h/DoctrineEnumBundle