Re: postgresql vs mysql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/23/07 15:47, Peter Eisentraut wrote:
> Brandon Aiken wrote:
>> That's why you make a table for every device or every measurement,
>> and then use a view to consolidate it. With updatable views, there's
>> no excuse not to.
>
> No, you put them all on one table and put nulls in places where no data
> is available. With real database systems, there's no excuse not to.

Each of the daily/hourly/etc temperature readings are independent.
Therefore they should each have their own row in the "meteorology
readings" table. I *think* that breaks 3NF.

This "should" be 3NF:

CREATE TABLE T_READING_TYPE (
READING_CODE CHAR(4) PRIMARY KEY,
READING_DESCRIP TEXT );

CREATE TABLE T_MET_READINGS (
_DATE DATE,
_HOUR SMALLINT CHECK (HOUR BETWEEN 0 AND 23),
READING_CODE CHAR(4) REFERENCES T_READING_TYPE(READING_CODE),
READING_VALUE NUMERIC(8,3),
PRIMARY KEY (_DATE, _HOUR)
);

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF32j3S9HxQb37XmcRAgsgAKC7m74VtyU5rnOI0gF2VXjHxk9kXgCfVY86
i5hgysDkC7EUJWlbGL+vyZM=
=RN+L
-----END PGP SIGNATURE-----