How to store data that depends on dynamic number of parameters 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
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"=&gt;"gum","store"=&gt;"711"','USD 0.49')
 ,(1,'"product"=&gt;"soda","store"=&gt;"711"','USD 0.99')
 ,(2,'"vehicle"=&gt;"prefect 351","fuel"=&gt;"nitromethane"','950kW')
 ,(2,'"vehicle"=&gt;"prefect 351","fuel"=&gt;"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"