On Wed, 2004-09-15 at 18:40, Adam Maloney wrote:
> > How do I allow for more workout types to be added and relate them to the
> > existing tables?
> > 
> > I can imagine having tables for users, daily_stats, workouts, and routes,
> > but how would I integrate new workout types? I don't think adding fields to
> > a general workout table would be the most elegant solution. Maybe I'm wrong.
> > (It's happened before.)
> 
> Create a workout_types table?
> 
> table workout_types
>   name varchar(64)
>   ...
> 
> Then just reference the workout_types.name in whatever other tables you
> reference a workout type?
> 
> 3rd normal form.  yum.
> 
> 

Only problem then is that each workout_type will have different
attributes to track.  Distance isn't going to do too much for the
jumprope workout_type.  Number of hops won't work for the Bicycle.

My best suggestion would be:

create table workout_type (
  wtID    serial not null primary key,
  wtName  varchar(25) not null,
);

-- waOrder is a sort order field for display purposes
create table workout_attribute (
  waID    serial not null primary key,
  waOrder integer not null,
  waName  varchar(25) not null
);

-- woPerson references another person table
create table workout (
  woID      serial not null primary key,
  wtID      integer not null,
  woPerson  integer not null,
  woDate    date
);

create table join_workout_attribute (
  jwaID     serial not null primary key,
  waID      integer not null,
  woID      integer not null,
  jwaValue  varchar(25)
);

create unique index idxJWA_1 
    on join_workout_attribute(waID, woID);

create view vw_Workout as
  select wtName,
         woPerson,
         woDate,
         jwaValue
    from workout_type wt,
         workout_attribute wa,
         workout wo,
         join_workout_attribute
   where wt.wtID = wo.wtID
     and jwa.wtID = wt.wtID
     and jwa.woID = wo.woID
;


Of course, this only gives you varchar attributes.  If you wanted to
test your muscle with PostgreSQL, you could add an integer column and
then create a rule to monitor to make sure you do not store 100 minutes
into a "face_color" attribute that should be storing purple.

Fun stuff!


_______________________________________________
TCLUG Mailing List - Minneapolis/St. Paul, Minnesota
Help beta test TCLUG's potential new home: http://plone.mn-linux.org
Got pictures for TCLUG? Beta test http://plone.mn-linux.org/gallery
tclug-list at mn-linux.org
https://mailman.real-time.com/mailman/listinfo/tclug-list