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!).
> > So, all this boils down to the simple question: Is it better (in terms of
> > indices) to have a seperate table for the time stamps and join the data
> > to it via an foreign key id field, or have both timestamps in each data
> > table? What if I would create more data tables?
>
> First instinct is to have timestamps, data-type and reference in one table
> and separate tables for each data-type for the data.
> Second instinct - have one table with null columns for unused types and
> take a hit on the wasted space.
>
> But - it really depends on how you're going to use the data. Will you be
> listing all entries between time X and Y or viewing entries of a specific
> type? Do you have any feel for what the common operations are going to be?
>
> - Richard Huxton
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[]
);
Where names would index into a table with the name->id mapping, because I
could throw out all unneeded data items after the SELECT(), thus having much
less data overhead (and much less records, we're talking ~500 data items per
packet - varying, of course ;-).
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)).
Thanks,Joerg
--
Leading SW developer - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com
