Data Model, alternative to EAV

Question

I have a Product Database, and I want to attach text, images, videos to the products. I also want that each entity (text, images or videos) have a tag, for further organisation on application.

I thought of using this model:

Content:

content_id|content_product_id|content_type|content_tag_id|content_url|content_title|content_text

Tag

tag_id|tag_name

This mean to use Entity(content_product_id) - Attribute(content_tag_id) - Value (content_url or content_title|content_text) Model.

After reading a lot, I understood that is a bad idea to use this modeling pattern (described as a database antipattern, unscalable and causing performance issues), have you an idea for an alternative method for this ?

I want to use Doctrine ORM, and I would like to find an method that will be easily compatible with that data mapper

Answer 1

I'd create a general table for any type of content:

CREATE TABLE ProductContents(
  content_id INT AUTO_INCREMENT PRIMARY KEY,
  content_type INT NOT NULL
  -- other general attributes like when it was created, by whom, etc.
);

For each text, image, or video, insert one row into this table. If you use an auto-increment primary key, this table is responsible for generating the id number.

For tags, now you simply have a many-to-many relationship between ProductContent and Tags. This is represented by an intersection table.

CREATE TABLE Tags (
  tag_id INT AUTO_INCREMENT PRIMARY KEY,
  tag TEXT NOT NULL
);

CREATE TABLE ProductContentTagged (
  content_id INT, 
  tag_id INT, 
  PRIMARY KEY (content_id, tag_id),
  FOREIGN KEY (content_id) REFERENCES ProductContents(content_id),
  FOREIGN KEY (tag_id) REFERENCES Tags(tag_id),
);

Then if you have any attributes specific to each type of content, create auxiliary tables for each type, with a one-to-one relationship to the content table.

CREATE TABLE ProductContentTexts (
  content_id INT PRIMARY KEY,
  content TEXT NOT NULL,
  FOREIGN KEY (content_id) REFERENCES ProductContents(content_id)
);

CREATE TABLE ProductContentImages (
  content_id INT PRIMARY KEY,
  image_path TEXT NOT NULL,
  FOREIGN KEY (content_id) REFERENCES ProductContents(content_id)
);

CREATE TABLE ProductContentVideos (
  content_id INT PRIMARY KEY,
  video_path TEXT NOT NULL,
  FOREIGN KEY (content_id) REFERENCES ProductContents(content_id)
);

Note these auxiliary tables don't have an auto-increment column. They don't need to -- they will always use the value that was generated by the ProductContents table, and you're responsible for inserting that value.