[thelist] 2 items - Database (application) structure and password access

databarn databarn at airmail.net
Sun Sep 16 14:17:46 CDT 2001


I've got a couple of questions on a database/application.  One is ab. tables and structure, the other is ab. passworded access to some of the tables.

The tables are below.
I=integer, N=numeric, D=decimal, V=varchar, B=bit/boolean, T=date/time.

ClientID - N - 9	StatDate - T - 4	ClientID - N - 9	DateEx - T - 4
FName - V - 30	ClientID - N - 9	SysID - N - 9		ClientID - N - 9
MName - V - 30	Weight - D - 9	System - V - 70	Session - N - 9
LName - V - 30	BMI - D - 9		Special - V - 1K	Start - T - 4
Alias - V - 30		StatNote - V - 2K	Settings - V - 255	Ended - T - 4
Born - T - 4					Defaults - V - 255
Height - D - 9					Created - T - 4
Hash - v - 50					Updated - T - 4
Created - T - 4
Updated - T - 4

Session - N - 9	Session - N - 9	Session - N - 9
SysID - N - 9		SysID - N - 9		Notes - V - 2K
Minutes - D - 9	Settings - V 50
Heart - D - 9		Weight - N - 9
Speed - D - 9		Reps - I - 2
Force - D - 9		RepNo - I - 2
Startup - V - 20	Type - B - 1
Other1 - V - 20
Other2 - V - 20

[ClientID] and SysID are autonumber primary keys for CLIENT and SYSTEM tables respectively.
[Session] is the autonumber primary key for SESSION
[Hash] is an MD5 hash of the client's password, and is the only thing the client can change in the CLIENT table.
[BMI] is Body Mass Index - a measure of body fat to muscle.
[Type] in WEIGHTS is 0 (machine) or 1 (free weights).

The client can add a STATS row once daily
The client can add a SESSION row more than once daily
The client can change the CARDIO and WEIGHTS table for the current Session only.  I.E., the client can add a row and edit the columns, but cannot edit a previous row.
The client can ADD a new System.
The client cannot delete.

This is currently in SQL Server 7 with an ASP front-end, but will be ported over to MySQL or PostgreSQL and PHP down the road.

OK, now to the questions.  I've considered adding a master system table, containing all the available equipment, with appropriate settings, etc., so that when the client adds an exercise, it can simply be picked from a list.  Cannot decide whether this would be worthwhile.  Downside is, of course, additional table and requisite storage, particularly when any given client will use only 30-35% of available equipment.  Upside would be the ability to assess overall usage by system, as well as various weight statistics by system.

Next, I'd like to include default configurations for the client, so that a daily session w/o changes to the defaults would be a matter of merely hitting a button/checkbox/whatever.  However, I cannot clearly see a good way to do this.  It would have to be editable by the client, so I can't make the value(s) a table default - can I?  Is there a way to modify a table's default entry for a column w/o dropping and recreating the table?  And, without the subsequent reentry of all data?

This is all in early development stages now, with only the tables defined, so I'm open to wide change if necessary (as long as I can understand it, of course <grin!>).

The password question is mostly one of procedure, I think.  The client has a hash of a password (which can be changed by the client) stored.  In order to enter data, the client must match that password.  (No client can see the data of another client - if I do this right, anyway!.)

However, there wants to be an admin access.  Would that be better as database or application level?  I'm thinking that application level would be sufficient, although that would probably require an admin table as well.  Admin access would be for two functions.  One would be data override if the client made a mistake on a session entry.  The other would be for summary reports (thus requiring access to multiple clients) of activity over time, by machine, etc..

Any thoughts you might have would really be appreciated.

Make a good day . . .
			 . . . barn
Never dirve faster than your Guardian Angel can fly.

More information about the thelist mailing list