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.
