[TCLUG] [OT] Database design problem

[Chris Mulcahy]

OK, here goes.  

BIG SPOILER WARNING!!

-- MySQL dump 9.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	4.0.21-nt

--
-- Table structure for table `person`
--

CREATE TABLE person (
  peID int(10) unsigned NOT NULL auto_increment,
  peName varchar(25) NOT NULL default '',
  PRIMARY KEY  (peID)
) TYPE=MyISAM;

--
-- Table structure for table `workout_type`
--

CREATE TABLE workout_type (
  wtID int(10) unsigned NOT NULL auto_increment,
  wtDescription varchar(25) NOT NULL default '',
  PRIMARY KEY  (wtID)
) TYPE=MyISAM;

--
-- Table structure for table `workout_attributes`
--

CREATE TABLE workout_attributes (
  waID int(10) unsigned NOT NULL auto_increment,
  wt_wtID int(10) unsigned NOT NULL default '0',
  waOrder int(10) unsigned NOT NULL default '0',
  waAttribDesc varchar(25) NOT NULL default '',
  waAttribType char(1) NOT NULL default 'V',
  PRIMARY KEY  (waID),
  UNIQUE KEY workout_attributes_uniqueness (wt_wtID,waOrder),
  KEY workout_attributes_FKIndex1 (wt_wtID)
) TYPE=MyISAM;

--
-- Table structure for table `join_workout_attributes`
--

CREATE TABLE join_workout_attributes (
  wa_waID int(10) unsigned NOT NULL default '0',
  wo_woID int(10) unsigned NOT NULL default '0',
  wa_ValueVarchar varchar(50) default NULL,
  wa_ValueInt int(10) unsigned default NULL,
  PRIMARY KEY  (wa_waID,wo_woID),
  KEY jwa_FK1 (wa_waID),
  KEY jwaFK2 (wo_woID)
) TYPE=MyISAM;

--
-- Table structure for table `workout`
--

CREATE TABLE workout (
  woID int(10) unsigned NOT NULL auto_increment,
  wt_wtID int(10) unsigned NOT NULL default '0',
  pe_peID int(10) unsigned NOT NULL default '0',
  woName varchar(25) default NULL,
  woDate date default NULL,
  PRIMARY KEY  (woID),
  KEY workout_FKIndex1 (pe_peID),
  KEY workout_FKIndex2 (wt_wtID)
) TYPE=MyISAM;

--
-- Dumping data for table `workout_type`
--

INSERT INTO workout_type VALUES (1,'Running');
INSERT INTO workout_type VALUES (2,'Jogging');
INSERT INTO workout_type VALUES (3,'Jumprope');
INSERT INTO workout_type VALUES (4,'Tae Kwon Do');

--
-- Dumping data for table `person`
--

INSERT INTO person VALUES (1,'Chris');
INSERT INTO person VALUES (2,'Barny Dino');

--
-- Dumping data for table `join_workout_attributes`
--

INSERT INTO join_workout_attributes VALUES (8,1,'',45);
INSERT INTO join_workout_attributes VALUES (9,1,'',60);
INSERT INTO join_workout_attributes VALUES (10,1,'Bruce Lee',0);
INSERT INTO join_workout_attributes VALUES (4,2,'',3);
INSERT INTO join_workout_attributes VALUES (5,2,'',8);
INSERT INTO join_workout_attributes VALUES (6,2,'',50);
INSERT INTO join_workout_attributes VALUES (7,2,'',45);

--
-- Dumping data for table `workout`
--

INSERT INTO workout VALUES (1,4,1,'Fun',NULL);
INSERT INTO workout VALUES (2,2,1,'Not Fun',NULL);
INSERT INTO workout VALUES (3,0,1,'Tuesday',NULL);

--
-- Dumping data for table `workout_attributes`
--

INSERT INTO workout_attributes VALUES (1,1,1,'Miles','I');
INSERT INTO workout_attributes VALUES (2,1,2,'Speed(MPH)','I');
INSERT INTO workout_attributes VALUES (3,1,3,'Route','V');
INSERT INTO workout_attributes VALUES (4,2,1,'Speed(MPH)','I');
INSERT INTO workout_attributes VALUES (5,2,2,'Miles','I');
INSERT INTO workout_attributes VALUES (6,2,3,'Heartrate','I');
INSERT INTO workout_attributes VALUES (7,2,4,'Minutes','I');
INSERT INTO workout_attributes VALUES (8,4,1,'Minutes','I');
INSERT INTO workout_attributes VALUES (9,4,2,'Heartrate','I');
INSERT INTO workout_attributes VALUES (10,4,3,'Sparring Partner','V');

-- OK, some fun now:
SELECT p.peName,
       wo.woName,
       wt.wtDescription,
       wo.woDate,
       wa.waAttribDesc,
       wa.waAttribType,
       jwa.wa_ValueVarchar,
       jwa.wa_ValueInt
  FROM person p,
       workout wo,
       join_workout_attributes jwa,
       workout_attributes wa,
       workout_type wt
 WHERE wo.pe_peID=p.peID
   AND jwa.wo_woID=wo.woID
   and jwa.wa_waID=wa.waID
   and wo.wt_wtID = wt.wtID;
 order by p.peName, wo.woDate, wt.wtDescription, wa.waOrder;

--
Chris Mulcahy

_______________________________________________
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