0

Consider an EAV table with separate columns for each datatype:

CREATE TABLE eav_values (
    id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_id INT NOT NULL,
    name VARCHAR(127) NOT NULL,
    type SET('varchar','int','datetime') NOT NULL,
    col_varchar VARCHAR(1023) NULL DEFAULT NULL,
    col_int INT NULL DEFAULT NULL,
    col_datetime DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB;

Now I'd like to store the username column for entity_id=5, which is a VARCHAR:

INSERT INTO eav_values (entity_id, name, type, col_varchar)
    VALUES (5, 'username', 'varchar', 'jeffatwood');

How would one construct the SELECT statement to select from the proper column with a single query?

SELECT (MAGIC HERE) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Might I be best off simply COALESCEing the values and carefully managing the data in application code? Is there no real performance penalty to such a SELECT?

SELECT COALESCE(col_varchar, col_int, col_datetime) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Or perhaps duplicate the data into a redundant read VARCHAR column for reading?

INSERT INTO eav_values (entity_id, name, type, col_varchar, read)
    VALUES (5, 'username', 'varchar', 'jeffatwood', 'jeffatwood');

SELECT read FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Note that I would like to use different datatypes for each value as in some cases I might need to index or perform datype-specific logic on certain columns, in those cases I'll know which datatype or I'll use a separate SELECT to get the column type.

  • Condolences -- You are walking down a torturous trail -- EAV. – Rick James Oct 3 '18 at 1:05
  • @RickJames: Pointers appreciated. So far I've been able to do what I need with subqueries but if you have any advice or tips they would be much appreciated. Thanks! – dotancohen Oct 4 '18 at 9:45
  • 1
    Follow the [eav] tag. – Rick James Oct 4 '18 at 15:28
  • 1
    Even more discussion in stackoverflow.org tagged [mysql] [entity-attribute-value] . – Rick James Oct 4 '18 at 18:19
1
CASE type 
WHEN 'varchar'  THEN col_varchar 
WHEN 'int'      THEN col_int 
WHEN 'datetime' THEN col_datetime 
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.