Darkstarone Darkstarone - 2 months ago 10
PHP Question

Doctrine/Symfony3 - Database columns with specific values

I'm trying to build a doctrine database for a Symfony application, and I'm not sure of the correct way of assembling the schema.

From here on in I'll use the Symfony3 tutorial

Product
and
Category
entities to illustrate my confusion:

// src/AppBundle/Entity/Category.php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
* @ORM\Entity
* @ORM\Table(name="category")
*/
class Category
{
/**
* @ORM\Column(type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;

/**
* @ORM\Column(type="string", length=100)
*/
private $type;

}


How do I define
type
so that it is one of a select few values (say:
["car", "bike", "bus"]
)?

My first initial thought was to have validation inside Symfony itself, such that before pushing to the database a check occurs.

However, this feels slightly hacky to me, and I'm wondering whether the correct procedure would be to enforce this constraint inside Doctrine itself.

Can someone shed some light on the best way to do this?

My problem further complicates itself when I have a one-to-many relationship between
Category
and
Product
, and
Product
itself has a
type
variable. My issue is that the valid values for
Product:type
differ depending on
Category:type
, for example:


  • Category:type = "car" -> Product:type = ["BMW" OR "Volvo"]

  • Category:type = "bike" -> Product:type = ["BMX" OR "Mountain Bike"]



Should this all be done using form/controller validation, or am I correct in assuming that it should be handled at a "lower" level?

Answer

I think you should create a separate product type table that stores the category relationship and is itself stored in the product instead of putting the category in the product.

So you would have

 Category 

 Product Type

 Product

Product type is many to one categories, a category may contain many product types, a Product is many to one product types, a product type may contain many products.

  Category Type -> Product Type -> Product

Make sense, there is no need to store the category in the product because it is inferred by the product type and its relationship.