EAV with multiple values per attribute

Question

I am working on e-commerce website with wide range of categories (TV, mobile phones, furniture, etc). Since there is such large amount of attributes I decided to use EAV model to store attributes and values. Here is my tables structure:

CREATE TABLE public.product_attribute
(
    name character varying(255) COLLATE pg_catalog."default",
    attribute_type character varying(255) COLLATE pg_catalog."default",
    id integer NOT NULL,
    CONSTRAINT product_attribute_pkey PRIMARY KEY (id)
)

CREATE TABLE public.product_attribute_value
(
    value_text text COLLATE pg_catalog."default",
    value_integer integer,
    value_boolean boolean,
    value_float double precision,
    value_richtext text COLLATE pg_catalog."default",
    value_date date,
    value_file character varying(255) COLLATE pg_catalog."default",
    value_image character varying(255) COLLATE pg_catalog."default",
    entity_object_id integer,
    attribute_id integer,
    product_id integer,
    id integer NOT NULL,
    CONSTRAINT product_attribute_value_pkey PRIMARY KEY (id),
    CONSTRAINT "product_attr_id" FOREIGN KEY (attribute_id)
        REFERENCES public.product_attribute (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Let's assume that I am adding a new TV.

Product
---------
id: 1
name: Sony TV

Product Attribute
-----------------
id: 1
name: Color

Product Attribute Value
-----------------------
id: 1
attribute_id: 1
value_text: white

The problem I am facing is that for some products (such as furniture) there are more than one attribute with same name (Color for example), not only that, I am also required to store the image photo url.

Product
---------
id: 2
name: Table

Product Attribute Value
-----------------------
id: 3
attribute_id: 1
value_text: Pattern 1
value_image: 01/a0/01a0sdadasfsdfsdfasdfasfdfsafs.jpg
=======================
id: 4
attribute_id: 1
value_text: Pattern 2
value_image: 01/a2/01a234234234234234324324234234.jpg

What is the best way to fetch products with all attributes? I am not worried a lot about query performance for filtering by attribute value (since all data synced to elasticsearch and fetched from there). But there are few millions of products. Is there any way to fetch each product with all attributes with one query (one product -> one row)?

I think JSONB is not the best idea to store this attributes, since they are updated frequently and I don't want to search and update millions of records to rename attribute name.

Answer 1

Your answer

more than one attribute with same name (Color for example),

So then create a different attribute with more specificity.

I am also required to store the image photo url.

This is just another attribute...

Product Attribute
-----------------
2 | photo_exterior

Product Attribute Value
-----------------------
id: 1
attribute_id: 2
value: 01/a0/01a0sdadasfsdfsdfasdfasfdfsafs.jpg

All of that said, I wouldn't be doing this unless I had to. I would be using JSONB.

What is the best way to fetch products with all attributes?

I'm unsure what kind of resultset you want. Are you asking how do you query an EAV? If you can make it, you can certainly do the joins to get the result set out of it.

Is there any way to fetch each product with all attributes with one query (one product -> one row)?

Yes, if you don't know how many attributes you have, you'll likely end up using hstore or jsonb though. See jsonb_agg()

X-Y problem?

Examining speed in order to get you to reasses JSONB and save your life. Create a million products, rename the attribute using jsonb.

CREATE TABLE products(id,name,data)
AS
        SELECT x, 'foo'::text, jsonb_build_object('color', 'blue') AS data
        FROM generate_series(1,1e6) AS gs(x);


UPDATE products
SET data = data-'color' || jsonb_build_object('colour', data->'color');
Time: 5063.010 ms

On my x230 laptop, with an SSD. That's 197 rows/ms? You recreating Amazon that you have to update faster than that?