Hi folkes,
this is my first message on this list.
I have quite a challenging problem and my own skills seem not to be
adequate for resolving it.
I have a relational model where the basic idea is to store data
vertically instead using traditional horizontal approach. So instead of
having a row in a table with n columns, I have n rows with value columns
for different data types. This makes the model very dynamic but also
difficult to use. I try to describe the (simplified) core of the model
(or at least the parts which have some meaning) in the following pseudo
definition:
Table values
ID serial pk
instanceID integer fk1 (never mind this)
parametertypeID integer fk2
value_integer integer
value_varchar character varying
value_date date
stamp timestamp
Table parameters
parametertypeID serial pk
typeid integer fk1
parameternameID integer fk2
Table parameternames
paramaternameID serial pk
parametername character varying
parameterdatatype integer or like enum(1,2,3) (this defines whether to
use value_integer, value_varchar or value_date)
From these three tables I would like to create a select statement where
the response is the following (where parameters.typeid = x and
values.timestamp = dd.mm.yyyy hh.mm.ss.xx)
instanceID integer
parameternames.parametername#1, value and datatype from value_integer,
value_varchar or value_date
parameternames.parametername#2, value and datatype from value_integer,
value_varchar or value_date
...
parameternames.parametername#n, value and datatype from value_integer,
value_varchar or value_date
Quite challanging, right?
- mika -
