Select/Update statements with multiple references and/or a procedure

I am writing a simple webapp for my house mate who is diabetic.
Currently she is using a Excel spreadsheet for it but it is problematic.
I said I could write something better so here I am.

The app only uses two tables which represent a single day's glucose
levels and insuling taken:

CREATE TABLE readings
(
`ID` int(10) unsigned NOT NULL auto_increment,
`Glucose` float NOT NULL default '0',
`Insulin` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (ID)
)

and

CREATE TABLE `records`
(
`DateOfReadings` date NOT NULL default '0000-00-00',
`BB_id` int(11) unsigned NOT NULL default '0',
`BL_id` int(11) unsigned NOT NULL default '0',
`BT_id` int(11) unsigned NOT NULL default '0',
`BBd_id` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`DateOfReadings`)
)

The query will select the records based on the date and then get the
relevant glucose/insulin readings from the relevant referenced ID's.
Things are further complicated that they may be some, none or all
readings have been filled in (usually though they will be entered as a
single block).

I was thinking of a select procedure that will first see if there is a
record for the day. If not then it will create the initial records with
the default values and return those. However does MySQL have a PL/SQL
style language?

Currently I have:

SELECT glucose, insulin
FROM readings, records
WHERE records.DateOfReadings='2005-02-10'
AND readings.ID=records.BB_id;

Which works for a single reading but I am having difficulty extending it
to an entire day.

It could be performed via 4 SELECT and 4 UPDATE statements but I would
rather do this properly and fire off a single select statement and
single update statement.

Thanks, Graham Reeds.


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Select/Update statements with multiple references and/or a procedure

Hey Graham,

I've done a fair bit of medical work, both for clients and for my wife
who needs to manage an inventory.

What's exactly the problem with using Excel?


On Fri, 11 Feb 2005 22:31:38 +0000, "Graham Reeds"
said:
> I am writing a simple webapp for my house mate who is diabetic.=20
> Currently she is using a Excel spreadsheet for it but it is problematic.=
=20
> I said I could write something better so here I am.
>=20
> The app only uses two tables which represent a single day's glucose=20
> levels and insuling taken:
>=20
> CREATE TABLE readings
> (
> `ID` int(10) unsigned NOT NULL auto_increment,
> `Glucose` float NOT NULL default '0',
> `Insulin` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (ID)
> )
>=20

What's the *OBJECT* here that's being tracked with the ID? Is it a
day's treatment? A reading? Why are they keyed? Simply to be able to
associate them with the "records" table?

> and
>=20
> CREATE TABLE `records`
> (
> `DateOfReadings` date NOT NULL default '0000-00-00',
> `BB_id` int(11) unsigned NOT NULL default '0',
> `BL_id` int(11) unsigned NOT NULL default '0',
> `BT_id` int(11) unsigned NOT NULL default '0',
> `BBd_id` int(11) unsigned NOT NULL default '0',
> PRIMARY KEY (`DateOfReadings`)
> )
>=20
> The query will select the records based on the date and then get the=20
> relevant glucose/insulin readings from the relevant referenced ID's.=20
> Things are further complicated that they may be some, none or all=20
> readings have been filled in (usually though they will be entered as a=20
> single block).

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,=20

CREATE TABLE readings (
DateOfReading TIMESTAMP NOT NULL DEFAULT,
TypeOfReading ENUM( 'no statement', 'BB', 'BL', 'BT', 'BBd' ) NOT
NULL,=20
Glucose FLOAT NOT NULL,
Insulin INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (DateOfReading, TypeOfReading)
)

>

>=20
> I was thinking of a select procedure that will first see if there is a=20
> record for the day. If not then it will create the initial records with=
=20
> the default values and return those. However does MySQL have a PL/SQL=20
> style language?

Usually this is done outside of MySQL with something like PHP, but quite
a lot can be done with SQL itself.
Almost anything can be done once stored procedures show up, but that's
not yet.

>=20
> Currently I have:
>=20
> SELECT glucose, insulin
> FROM readings, records
> WHERE records.DateOfReadings=3D'2005-02-10'
> AND readings.ID=3Drecords.BB_id;
>=20
> Which works for a single reading but I am having difficulty extending it=
=20
> to an entire day.
>=20
> It could be performed via 4 SELECT and 4 UPDATE statements but I would=20
> rather do this properly and fire off a single select statement and=20
> single update statement.

[snip]

=20
--=20
Jan Theodore Galkowski (o°) =

jtgalkowski@alum.mit.edu
http://www.smalltalkidiom.net



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Select/Update statements with multiple references and/or a procedure

Jan Theodore Galkowski wrote:
> Hey Graham,
>
> I've done a fair bit of medical work, both for clients and for my wife
> who needs to manage an inventory.
>
> What's exactly the problem with using Excel?

Not exactly interactive but it does make a nice graph. Everytime she
adds a new day to the month she has to add the relevant BB (Before
Breakfast), BL (Lunch), BT (Tea), BBd (Bed) manually. I've suggested
things like templates to help her but I opened my mouth and said "I
could do better".

> On Fri, 11 Feb 2005 22:31:38 +0000, "Graham Reeds"
> said:
>
>>I am writing a simple webapp for my house mate who is diabetic.
>>Currently she is using a Excel spreadsheet for it but it is problematic.
>> I said I could write something better so here I am.
>>
>>The app only uses two tables which represent a single day's glucose
>>levels and insuling taken:
>>
>>CREATE TABLE readings
>>(
>> `ID` int(10) unsigned NOT NULL auto_increment,
>> `Glucose` float NOT NULL default '0',
>> `Insulin` int(10) unsigned NOT NULL default '0',
>> PRIMARY KEY (ID)
>>)
>>
>
>
> What's the *OBJECT* here that's being tracked with the ID? Is it a
> day's treatment? A reading? Why are they keyed? Simply to be able to
> associate them with the "records" table?

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?).

>>I was thinking of a select procedure that will first see if there is a
>>record for the day. If not then it will create the initial records with
>>the default values and return those. However does MySQL have a PL/SQL
>>style language?
>
> Usually this is done outside of MySQL with something like PHP, but quite
> a lot can be done with SQL itself.
> Almost anything can be done once stored procedures show up, but that's
> not yet.

Thanks for you help.

Graham Reeds.


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Select/Update statements with multiple references and/or a procedure

On Sat, 12 Feb 2005 14:40:49 +0000, "Graham Reeds"
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=gcdmw-win32@m.gmane.org