How to normalize this table

  • Thread starter Thread starter CoachBarkerOJPW
  • Start date Start date
C

CoachBarkerOJPW

I have created asmall application in vb.net. Not relevent here I know , but
the table structure of one of the tables is. Right now I have three tables,
tblPatient, tblGlucose and tblExercise. I need to come up with a better
solution for the exercise table. Right now I just update the table for
everyday. Right now the records are kept based on the WeekBeginning field, so
I want 7 records before I start a new row.
The weekdays in the table are to give the user of the application a visual
sense in the form of when they have exercised.

Any suggestions?

tblExercise
ExerciseLogID
PatientID
WeekBeginning
DateEntered
ExerciseYes
ExerciseNo
MinutesExercised
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Walk
Jog
Run
Swim
Bicycle
WhyNot
WeeksTotal
 
I want 7 records before I start a new row.
Click to expand...

There's one problem. Use a separate record for each day's readings rather
than
seven readings in one record. To normalize, think long rather than wide.

Tom Lake
 
I have created asmall application in vb.net. Not relevent here I know , but
the table structure of one of the tables is. Right now I have three tables,
tblPatient, tblGlucose and tblExercise. I need to come up with a better
solution for the exercise table. Right now I just update the table for
everyday. Right now the records are kept based on the WeekBeginning field, so
I want 7 records before I start a new row.
The weekdays in the table are to give the user of the application a visual
sense in the form of when they have exercised.
Click to expand...

Use one record per day, or - better - per exercise event (somebody might want
to exercise twice a day after all!). You can create a (non-editable) Crosstab
query for display purposes, but don't let that data display requirement drive
you into an improper table design.

John W. Vinson [MVP]
 
Tom Lake said:
Are you kidding?!? 8^)
Click to expand...

Sure one walk to the coffee shop, casino, bar for video lottery
terminal or drink(s), corner store for smokes, liquor store and back.
That's twice, right?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Going with John's suggestion, I'd probably do something like:

tblExercise:
ExerciseLogID
PatientID
DateEntered
ExerTypeID (FK to tblExerTypes)
Exer_Duration (long integer - minutes)
Comment (for ExerTypeName = None)

I like the idea of an Exercise types table because it allows you to add
exercise types at a later time without having to add a column to your table
structure.
tblExerTypes:
ExerTypeID (Long integer)
ExerTypeName (walk, jog, run, swim, bike, None)
ExerTypeSort (Integer - used to sort these values)

If you need the WeekStart, you can get it for any date using the expression:

WeekStart: [DateEntered] - Weekday([DateEntered]) + 1

HTH
Dale
 
Back
Top