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