2

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.

Example:

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 '18 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 ... – Erwin Brandstetter Dec 20 '18 at 23:44
  • create one table with columns common to all clients and a JSONB column to store the client specific attributes and values – a_horse_with_no_name Dec 21 '18 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). – Noé Malzieu Dec 21 '18 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. – Noé Malzieu Dec 21 '18 at 8:55
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 – a_horse_with_no_name Dec 21 '18 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 – Noé Malzieu Dec 21 '18 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 '18 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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