Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
Subject: Re: Table design question...
Date: Tue, 6 Aug 2002 13:59:53 +0100
User-Agent: KMail/1.4.1
Cc: pgsql-sql@postgresql.org
References: <200208060921.LAA20528@localhost.localdomain>
	<200208061046.04142.dev@archonet.com>
	<200208061039.MAA20640@localhost.localdomain>
In-Reply-To: <200208061039.MAA20640@localhost.localdomain>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200208061359.53726.dev@archonet.com>

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=
=20
float) I'd go for (B) since that makes things nice and easy. On the other=
=20
hand, if you want to export different types in one query you'll need to do =
a=20
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=
=20
efficient. You can always hide the implementation details behind a view and=
=20
some functions.

- Richard Huxton