Re: Table design question...

On Tuesday 06 Aug 2002 11:35 am, Joerg Hessdoerfer wrote:
> Thank you for your help,
>
> On Tuesday 06 August 2002 11:46, you wrote:
> > On Tuesday 06 Aug 2002 10:17 am, Joerg Hessdoerfer wrote:
> >
> > [ data logging with variable formats for data some of which might be
> > arrays for a single item - nice :-/ ]
>
> Yep. This is what it boils down to - and noone can tell me in advance what
> the data type for each entry really is... it can even change mid-operation
> (now how's that for design!).

> Well, the access is mainly 'export dumps' in a special format, where
> varying data items need to be exported from between two time stamps. So
> there was my idea using the arrays coming from. I'd have something like
>
> CREATE TABLE data_float(
> times_id int4 references times(id),
> names int4[],
> values float[]
> );

> But is the above better than
>
> CREATE TABLE data_float(
> scet timestamp without time zone,
> recv timestamp without time zone,
> names int4[],
> values float[]
> );
>
> given I have at least two data tables (to acommodate for the 'array in one
> value' data items, which are much less common (1/250, roughly)).

If your exports are all of one type (i.e. all arrays or all integer or all
float) I'd go for (B) since that makes things nice and easy. On the other
hand, if you want to export different types in one query you'll need to do a
union unless you put all possible fields in one table:

CREATE TABLE data_all (
scet timestamp,
recv timestamp,
data_type char(1) CHECK (data_type IN ('i','f','a')),
intval int4,
floatval float,
arr_names int4[],
arr_values int4[]
);

If you go the route of (A) or (B) I'd run a few tests to see which is more
efficient. You can always hide the implementation details behind a view and
some functions.

- Richard Huxton