Table design - your opinion?

From: Peter Laursen <ptl_at_edbgruppen.dk>
Date: 14 Jun 1999 09:03:19 GMT
Message-ID: <01beb64d$950592a0$2c289a0a@apollo>

We are having some discussions here about a possible redesign of some tables, to get better performance. I would like to get some pro's and con's.

Readings from electricity-meters are collected with 15-minut intervals and stored in table SAMPLE. No. of rows will be in the millions: table SAMPLE:
SAMPLE_RECNUM number (from a sequence)

SAMPLETIME         date
VALUE                   number

METER_RECNUM number (foreign key to table METER)

However, automatic value-checking might decide that the read value is not correct, and insert an calculated value into: No. of rows will be in the thousands:
table SAMPLE_QUALITY:
SAMPLE__QUALITY_RECNUM number (from a sequence)

SAMPLETIME         date
VALUE                   number

The user has the opportunity to overrule either: No of rows will be few: tabel SAMPLE_USER
SAMPLE_USER_RECNUM number (from a sequence)

SAMPLETIME          date
VALUE                    number

SAMPLE_RECNUM number(foreign key to table SAMPLE)

When selecting the rule is: if at a given sampletime a value is present in sample_user this is the value to use, else look in sample_quality and last in sample. A view implements this:
(In the above tables I have simplified and ommited details, here is the actual code:)
CREATE OR REPLACE VIEW LASTSAMPLE ( SAMPLE_RECNUM, UPDATEDAT, UPDATEDBY, VALUE, SAMPLETIME, METER_RECNUM, LATEST, SENTTOELTRA ) AS select L.sample_recnum, L.updatedat, L.updatedby,

       decode(null, U.value, decode(null, Q.value, L.value, Q.value), u.value) value,

       L.sampletime, L.Meter_Recnum, L.latest, L.SentToEltra from sample L, sample_user U, lastsample_quality Q

where L.latest = 1
and   l.meter_recnum = q.meter_recnum  (+)
and   L.sample_recnum = U.sample_recnum (+)
and   L.sampletime = Q.Sampletime (+) 

Well, three outer joins might be bad for performence

The new proposal is to integrate all three tables into one and skip the view:

NEW_SAMPLE:
SAMPLE_RECNUM number (from a sequence)

SAMPLETIME         date
READ_VALUE         number
QUALITY_VALUE    number
USER_VALUE         number

METER_RECNUM number (foreign key to table METER)

The objection is that this table will be filled with NULL's and only very sparsely with data in columns QUALITY_VALUE and USER_VALUE.

What would the pro's and con's of the new design be? With indexes as needed will we get better performence with the new design - both insert and select performence must be good.

Thanks for reading all the way down here ! Peter Received on Mon Jun 14 1999 - 04:03:19 CDT