mysqldatabaseentity-attribute-value

MySQL: how to convert to EAV - Part 3?


Previous Related Posts:

Given a table:

TABLE: foo
===============================
| id | first_name | last_name |
===============================
| 1  | John       | Doe       |
| 2  | Jane       | Smith     |
| 3  | Ronald     | McDonald  |
-------------------------------

How do I take this table and convert it to these tables (an EAV implementation)?:

TABLE: attribute
===========================
| id | fk_id | attribute  |
===========================
| 1  | 100   | first_name |
| 2  | 100   | last_name  |
---------------------------

TABLE: value
=========================================
| id | attribute_id | row_id | value    |
=========================================
| 1  | 1            | 1      | John     |
| 2  | 2            | 1      | Doe      |
| 3  | 1            | 2      | Jane     |
| 4  | 2            | 2      | Smith    |
| 5  | 1            | 3      | Ronald   |
| 6  | 2            | 3      | McDonald |
-----------------------------------------

NOTES:

UPDATE: Also, how do I query the EAV tables so that I can make it look like table foo again.


Solution

  • I think your only hope is if you use the foo table. bar is essentially useless without the ID.

    Try something like this (assuming attribute.id is an auto-increment primary key)

    INSERT INTO `attribute` (`fk_id`, `attribute`)
    VALUES (100, 'first_name');
    
    INSERT INTO `value` (`attribute_id`, `row_id`, `value`)
    SELECT LAST_INSERT_ID(), `id`, `first_name`
    FROM `foo`;
    
    INSERT INTO `attribute` (`fk_id`, `attribute`)
    VALUES (100, 'last_name');
    
    INSERT INTO `value` (`attribute_id`, `row_id`, `value`)
    SELECT LAST_INSERT_ID(), `id`, `last_name`
    FROM `foo`;
    

    To reconstruct the foo table, try this

    SELECT
        `fn`.`row_id` AS `id`,
        `fn`.`value` AS `first_name`,
        `ln`.`value` AS `last_name`
    FROM `value` `fn`
    INNER JOIN `attribute` `fn_a`
        ON `fn`.`attribute_id` = `fn_a`.`id`
        AND `fn_a`.`attribute` = 'first_name'
    INNER JOIN `value` `ln`
        ON `fn`.`row_id` = `ln`.`row_id`
    INNER JOIN `attribute` `ln_a`
        ON `ln`.`attribute_id` = `ln_a`.`id`
        AND `ln_a`.`attribute` = 'last_name'
    

    Ergh, thanks for reminding me why I hate this pattern