3

I have 3 tables:

  1. Products, containing - id, name and description
  2. Attributes, containing - id, name
  3. prod_attri_rel, containing - id, prod_id, attri_id and value

    1. Products looks like:
 1 | test | just a description for test
 2 | test2| just another description
  1. Attributes looks like:
 1 | height
 3 | length
 2 | width
 4 | power
 5 | id
  1. prod_attri_rel looks like:
1 | 1 | 1 | 2
2 | 1 | 2 | 25
3 | 1 | 3 | 20
4 | 2 | 1 | 2
5 | 2 | 2 | 25
6 | 2 | 3 | 20
7 | 2 | 4 | UBEC
9 | 2 | 5 | BC2212-850

After looking at https://dba.stackexchange.com/a/33307/48727 and https://stackoverflow.com/a/695860/1820180

Now I would like to get this result:

id | name | height | width | length | maybe other attr | so on and so forth
  1| test | 2      | 25    | 20     | ...

When I use the following query:

SELECT p.id, p.name , par.value, a.name FROM products p 
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id

I get:

id | name | value| name
1  | test | 2    | height
1  | test | 25   | width
1  | test | 20   | length

Then when I tried:

SELECT p.id, p.name , group_concat( par.value ), group_concat( a.name ) FROM products p 
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id GROUP BY p.name

I get a better result but not really it:

id | name | value   | name
1  | test | 25,2,20 | width,height,length

Last I found this https://stackoverflow.com/a/10926106/1820180 but I did not understand it enough to adapt it to my problem.

Any help in this matter would be appreciated, even if it is finding another method, or getting it to work.

Purpose: This is a database for different parts to RC toys, such as motors, ESC's wheels, propellers and such, all have different attributes, but all motors have same attributes, all wheels have same attributes.

3
  • The design pattern you describe is commonly known as Entity Attribte Value or EAV. There are many, many web postings discussing it. Be aware query performance may degrade as row counts get large. Dec 18, 2015 at 8:51
  • @MichaelGreen so am i using the wrong approach here? Should i use another design pattern? Dec 18, 2015 at 14:40
  • It is not necessarily wrong. It can be made to work. Mostly I wanted to give you a well-known term you can search for. Some people love EAV for its flexibility. Some hate it because it subverts the DBMS. I've used it effectively for prototypes. Once "Values" table got into tens of millions of rows, it was time to re-write, however. A PIVOT, such as yours, was the final straw that forced the re-work. Dec 20, 2015 at 10:12

2 Answers 2

2

The output of your query is fine but you must then pivot the data using GROUP BY. (SQL Fiddle)

Query:

SELECT p.id, p.name 
  , MAX(IF(a.name='height',par.val,0)) As 'height'
  , MAX(IF(a.name='width',par.val,0)) As 'width'
  , MAX(IF(a.name='length',par.val,0)) As 'length'
FROM products p 
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id
GROUP BY p.id, p.name
;

Output:

id  name    height  length  width
1   test    2       20      25
2   test2   3       35      30

Data:

CREATE TABLE Products(id int, name varchar(50), info varchar(50));

INSERT INTO Products(id, name, info) VALUES
(1, 'test', 'just a description for test')
, (2, 'test2', 'just another description');

CREATE TABLE Attributes(id int, name varchar(50));
INSERT INTO Attributes(id,name) values
    (1, 'height')
    , (3, 'length')
    , (2, 'width')
    , (4, 'power')
    , (5, 'id');

CREATE TABLE prod_attri_rel(id int, prod_id int, attri_id int, val varchar(20));
INSERT INTO prod_attri_rel(id, prod_id, attri_id, val) values
    (1, 1, 1, '2')
    , (2, 1, 2, '25')
    , (3, 1, 3, '20')
    , (4, 2, 1, '3')
    , (5, 2, 2, '35')
    , (6, 2, 3, '30')
    , (7, 2, 4, 'UBEC')
    , (9, 2, 5, 'BC2212-850');
4
  • Some of the attributes could be item identification number such as: BC2212-850 and maybe power conversion features like UBEC, Linear and OPTO. Dec 17, 2015 at 19:12
  • @Magic-Mouse see the update with your new data. I used MAX instead of SUM and I replace int by varchar. Dec 18, 2015 at 8:37
  • It looks pretty good but the query does still not use the table value as column names, meaning if i add in more attributes i have to change everything, am i using the wrong approach ? Dec 18, 2015 at 14:36
  • you need 1 line with MAX for each possible value. I only created 3 for height, width and length. You must use dynamic SQL if you want to create it automatically without writing each name one by one Dec 18, 2015 at 14:39
1

Michael Green sent me on track with the EAV pattern made it searchable now i had a name for it.

The solution for the problem is:

SELECT 
    a.entity AS id,
    a.value AS title,
    b.value AS description,
    c.value AS height,
    d.value AS width,
    e.value AS length
FROM
    attributes a
        JOIN
    attributes b USING (entity)
        JOIN
    attributes c USING (entity)
        JOIN
    attributes d USING (entity)
        JOIN
    attributes e USING (entity)
WHERE
    a.attribute = 'name'
        AND b.attribute = 'description'
        AND c.attribute = 'height'
        AND d.attribute = 'length'
        AND e.attribute = 'width'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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