Re: Select/Update statements with multiple references and/or a
procedure 2005-02-12 - By Jan Theodore Galkowski
Back
On Sat, 12 Feb 2005 14:40:49 +0000, "Graham Reeds"
<grahamr@(protected)> said:
> Jan Theodore Galkowski wrote:
> > Hey Graham,
> >
[snip]
>
> Yes. You take 4 readings a day, once before breakfast - the BB_id in
> records, once before lunch, tea and bed for the glucose levels and
> also record how much insulin you take. The reason they are keyed is
> because I thought that would be sensible way to do it.
>
> > Why not have just a readings table and characterize a kind of
> > reading as BB, BL, BT, and BBd? Can date or time stamp each of
> > these. That is, [snip]
>
> I guess that would work - I was thinking along the lines of separating
> in their logical blocks (normalising?).
>
Graham,
The basic action, then, is a measurement or "reading". The kind of
measurement here is actually a temporal indicator. Admittedly there is
some significance to where that is in terms of glucose levels, so it
should be preserved as a datum, but it does mean grabbing all four into
a "day" unit isn't proper logical design here. It is better to use the
schema I provided in my response and make the TIMESTAMP and ENUM be the
primary key.
It might be preferable to have a DATE instead of a TIMESTAMP but the
virtues of the TIMESTAMP are that it provides "experimental
robustness", that is, an independent check on when the information was
entered and that in MySQL if an INSERT is done without a value for a
NOT NULL attribute that's a TIMESTAMP (at least the first in a record),
the time the INSERT executes gets entered. You can always grab the
date portion of the TIMESTAMP by using DATE_FORMAT(). Ask for details
if you need them.
As in most data collection systems, records should probably never be
deleted, even if incorrect, so the only other thing I might add is a
validity field. So, you end up with:
CREATE TABLE readings (
TimeOfReading TIMESTAMP NOT NULL,
PhaseOfDay ENUM( 'no statement', 'BB', 'BL', 'BT', 'BBd' ) NOT NULL,
Glucose FLOAT NOT NULL,
Insulin INT(10) UNSIGNED NOT NULL,
Validity ENUM ( 'valid', 'invalid' ) NOT NULL,
PRIMARY KEY (TimeOfReading, PhaseOfDay)
)
Alternatively, could do
CREATE TABLE readings (
DateOfReading DATE NOT NULL,
PhaseOfDay ENUM( 'no statement', 'BB', 'BL', 'BT', 'BBd' ) NOT NULL,
Glucose FLOAT NOT NULL,
Insulin INT(10) UNSIGNED NOT NULL,
Validity ENUM ( 'valid', 'invalid' ) NOT NULL,
touched TIMESTAMP NOT NULL,
PRIMARY KEY (DateOfReading, PhaseOfDay)
)
which provides the same capability.
[snip]
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=mysql@(protected)
Earn $52 per hosting referral at Lunarpages.
|
|
