How Can I Pivot a MySQL Entity-Attribute-Value (EAV) Schema for Efficient Data Retrieval?

Pivoting MySQL Entity-Attribute-Value Schema

The need to pivot an entity-attribute-value (EAV) schema arises when there are many custom metadata fields that cannot be determined beforehand. To address this, you can create tables to store common metadata, optional attributes, and attribute values for files.

Consider the following schema:

CREATE TABLE FileBase (
    id VARCHAR(32) PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(255),
    author VARCHAR(255),
    created DATETIME NOT NULL,
);

CREATE TABLE Attributes (
    id VARCHAR(32) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL
);

CREATE TABLE FileAttributes (
    sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    fileId VARCHAR(32) NOT NULL,
    attributeId VARCHAR(32) NOT NULL,
    attributeValue VARCHAR(255) NOT NULL,
    FOREIGN KEY fileId REFERENCES FileBase (id),
    FOREIGN KEY attributeId REFERENCES Attributes (id)
);

To retrieve the data in the desired format, you can leverage the GROUP_CONCAT() function in MySQL:

The above is the detailed content of How Can I Pivot a MySQL Entity-Attribute-Value (EAV) Schema for Efficient Data Retrieval?. For more information, please follow other related articles on the PHP Chinese website!