On Wed, 2004-09-15 at 22:18, Tim Wilson wrote:
> On Wednesday 15 September 2004 08:09 pm, Chris wrote:
>
> > 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)
> > );
>
> This join_workout_attribute table is the biggest piece I was missing. I think
> I understand where you're going with this, but I'm not sure I understand how
> you would store the attributes that are supposed to go with a jogging
> workout, for example.
>
> Let's say for such a workout I want to store date, distance, elapsed time (in
> seconds, say), average heart rate, and workout notes. You're saying that
> those attributes would be entered into the workout_attribute table and joined
> to each workout using the join_workout_attribute table, right?
>
> So where would I store the information about the standard attributes that all
> jogging workouts contain? And how would I store the type of data to expect
> for each attribute? I don't see how that would work in the workout_attribute
> table. For example, average heart rate would be an integer and workout notes
> would be a text field.
>
> This is great info. I may not be expert at it yet, but I really enjoy the
> process of mapping real life data to these sorts of abstract relationships.
>
> -Tim
I see I goobered one part.
alter table workout_attribute add wtID;
insert into workout_type (wtName) values ('Walking');
insert into workout_type (wtName) values ('Jogging');
insert into workout_type (wtName) values ('Biking');
select * from workout_type;
wtID wtName
1 Walking
2 Jogging
3 Biking
insert into workout_attribute (wtID, waOrder, waName) values (1, 1,
'Distance');
insert into workout_attribute (wtID, waOrder, waName) values (1, 2,
'Speed');
insert into workout_attribute (wtID, waOrder, waName) values (2, 1,
'Distance');
insert into workout_attribute (wtID, waOrder, waName) values (2, 2,
'Speed');
insert into workout_attribute (wtID, waOrder, waName) values (2, 3,
'Steps');
select * from workout_attribute;
waID wtID waOrder waName
1 1 1 Distance
2 1 2 Speed
3 2 1 Distance
4 2 2 Speed
5 2 3 Steps
insert into workout (wtID, woPerson) values (1, 1);
insert into workout (wtID, woPerson) values (2, 1);
insert into join_workout_attribute (woID, waID, jwaValue) values (1, 1,
'5 mi');
insert into join_workout_attribute (woID, waID, jwaValue) values (1, 2,
'1.6 MPH');
insert into join_workout_attribute (woID, waID, jwaValue) values (2, 1,
'2 mi');
insert into join_workout_attribute (woID, waID, jwaValue) values (2, 2,
'1400');
You do have the problem of varchar/integer values. On the
workout_attribute table, you can store another column which identifies
what type of data you are storing, then in the join_workout_attribute
table, you would have two value columns, one for integer, one for
varchar. You could use PostgreSQL rules to make sure you don't
accidentally stuff a value into the integer column when the attribute
should be a varchar.
I'll put together a much more useful demo in the morning. I have to get
to bed!
Good luck! This is very 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