I can't get my head around this problem. I'm trying to find a good way to store this in a PostgreSQL database.

Each client of my company needs to visualize data that depends on a dynamic number of parameters.


clientA wants to store the prices of its products. A price is defined by product_id and store_id. I need a way to query the database, giving the product_id and store_id and get back a price.

clientB wants to store the power of its cars. A power is defined by car_id, car_variation and fuel_type. I need a way to query the database, giving the car_id, car_variation and fuel_type and get back a power.

Each client must be able to define its own "dimensions" or "parameters" that define the data they want to vizualize.

I can either :

  • put all the data in the same table, with A LOT of empty columns for each row ==> seems dumb
  • create a new table for each client, with only the right columns in each of them? I'm not used to having a table per client but maybe it's the way to go
  • a third way I did not think of that tackles this exact problem
  • Have you considered an EAV model? en.wikipedia.org/wiki/…
    – McNets
    Dec 20, 2018 at 18:36
  • Depends on information that's not in our question. How many clients, how many dimensions, how many rows, how much traffic, insert / update / delete, priorities ... Dec 20, 2018 at 23:44
  • create one table with columns common to all clients and a JSONB column to store the client specific attributes and values
    – user1822
    Dec 21, 2018 at 8:46
  • @McNets do you think the EAV model works? The main thing I need is querying according to multiple attributes value (given these attributes values, what is the entity? Not sure EAV is made for this). Dec 21, 2018 at 8:54
  • @ErwinBrandstetter yeah you're right. Around 100 thousand rows, a few new clients a week (B2B company) and a few dozens for now, not a lot of traffic, mainly read operations once it's written. Dec 21, 2018 at 8:55

1 Answer 1


maybe you want a table that has a composite type as part of the key.

create extension hstore;

create table stats (client int, key hstore, value text , primary key (client, key));

insert into stats values 
  (1,'"product"=>"gum","store"=>"711"','USD 0.49')
 ,(1,'"product"=>"soda","store"=>"711"','USD 0.99')
 ,(2,'"vehicle"=>"prefect 351","fuel"=>"nitromethane"','950kW')
 ,(2,'"vehicle"=>"prefect 351","fuel"=>"ULP-97RON"','600kW')

select * from stats where client=2 and key=hstore(array['vehicle','prefect 351','fuel','nitromethane']);

or perhaps with the value column as hstore also, perhaps he wants to store "loudness" and "power"

  • 1
    Nowadays I would probably go for a JSONB column, rather than hstore
    – user1822
    Dec 21, 2018 at 8:45
  • Ok so basically a JSONB or hstore to store all varying data. That may work since JSONB can be indexed… May validate this answer just waiting to see if smth else comes up Dec 21, 2018 at 8:59
  • I chose hstore because it reorders the elements, meaning '{"a"=>"1","b"=>"2"}' = '{"b"=>"2","a"=>"1"}' which is probably a useful feature. (for a key) you can do decomposed indexes on hstore too if needed.
    – Jasen
    Dec 21, 2018 at 17:58

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.