Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Good day. I'm designing a database for a research project where we're predicting whether or not young female athletes will rupture their ACL(s) based on the nature of a test jump recorded with a Microsoft Kinect. I've got the basics of the table down, but I've gotten rusty since database class last semester.

To make things more modular (highly underused buzzword... modular), I've split things up into separate tables. The nature of the data revolves around each test subject, so the main table is called "TestSubject". It contains fields about aspects of the test subject- her subjectId (PK), her date of birth, and potentially her erm... date of womanhood, if you will.

Next is a SubjectJumps table. Each test subject will have at least one (but ideally, three) jump table, so it's a one-to-n relationship here. The subjectId from TestSubject is imported as the primary key. Another primary key exists in the form of a jumpDate field, which contains a time stamp. We then have the jump metrics recorded from the Kinect, which I'll spare you the details of.

After that is a SubjectSports table, which records the information related to the test subject's participation in high school sports. Again, the subjectId is imported from TestSubject as a primary key. Like SubjectJumps, it's a one-to-n relationship, and since we're recording information about athletes that will be participating in at least one sports season over the course of the study, it's a necessary relationship. Aside from the primary key, a field exists for a sport.

Finally, we come to the table I have a question about. This table is named PreviousInjury. Athletes who have injured their ACLs in the past are more likely to do so in the future (and not necessarily on the same knee). The primary key subjectId is imported in from TestSubject, and fields for which knee was injured and on what date exist as well. Multiple relations can exist, or none can exist.. it is not a given that the athlete has injured her knee before. Since this is the case, is it an identifying or non-identifying relationship?

Included for your perusal are links to a photo of my (horribly handwritten) ERD, the SQL script, as well as the MySQL Workbench EER model. I look forward to your answers on this, as well as any other thoughts you have on the nature of the database itself.

Hand-drawn ERD

MySQL Workbench file

SQL script:

-- -----------------------------------------------------
-- Table `mydb`.`TestSubject`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`TestSubject` (
  `subjectId` INT NOT NULL ,
  `subjectDOB` DATE NULL ,
  `subjectMenarche` DATE NULL ,
  PRIMARY KEY (`subjectId`) ,
  UNIQUE INDEX `subjectId_UNIQUE` (`subjectId` ASC) )
ENGINE = InnoDB
COMMENT = '     ';


-- -----------------------------------------------------
-- Table `mydb`.`SubjectJumps`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`SubjectJumps` (
  `jumpDate` DATETIME NOT NULL ,
  `rightAnkleRatio` DECIMAL(3) NULL ,
  `leftAnkleRatio` DECIMAL(3) NULL ,
  `rightKneeValgus` DECIMAL(3) NULL ,
  `leftKneeValgus` DECIMAL(3) NULL ,
  `rightKneeFlexion` DECIMAL(3) NULL ,
  `leftKneeFlexion` DECIMAL(3) NULL ,
  `testSubject_subjectId` INT NOT NULL ,
  PRIMARY KEY (`jumpDate`, `testSubject_subjectId`) ,
  INDEX `fk_subjectJumps_testSubject_idx` (`testSubject_subjectId` ASC) ,
  CONSTRAINT `fk_subjectJumps_testSubject`
    FOREIGN KEY (`testSubject_subjectId` )
    REFERENCES `mydb`.`TestSubject` (`subjectId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`SubjectSports`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`SubjectSports` (
  `sport` VARCHAR(10) NOT NULL ,
  `TestSubject_subjectId` INT NOT NULL ,
  PRIMARY KEY (`TestSubject_subjectId`) ,
  CONSTRAINT `fk_SubjectSports_TestSubject1`
    FOREIGN KEY (`TestSubject_subjectId` )
    REFERENCES `mydb`.`TestSubject` (`subjectId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`PreviousInjury`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`PreviousInjury` (
  `injuryDate` DATE NULL ,
  `injuryKnee` VARCHAR(5) NULL ,
  `TestSubject_subjectId` INT NOT NULL ,
  PRIMARY KEY (`TestSubject_subjectId`) ,
  CONSTRAINT `fk_PreviousInjury_TestSubject1`
    FOREIGN KEY (`TestSubject_subjectId` )
    REFERENCES `mydb`.`TestSubject` (`subjectId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
share|improve this question
    
Adding third link due to StackExchange's terrific rep system. SQL Script –  nerdenator Jul 10 '13 at 15:44
    
Apart from some redundant indexes, the issue I see is the primary key of PreviousInjury. You say that a "subject" can have 0, 1 or more injuries. If that's the case, then the primary key cannot be the TestSubject_subjectId, at least not alone. Either define some composite key as the PK (maybe (TestSubject_subjectId, injuryDate) although not even this sounds exactly right if a person can have multiple injuries the same date) or some other (surrogate column) as the PK. And why the name PreviousInjury? Why not just Injury? Or SubjectInjuries to be consistent? –  ypercube Jul 10 '13 at 16:32
    
The same applies to the "Sports" table. As you have it now, a subject can have only 1 sport associated. –  ypercube Jul 10 '13 at 16:34
    
Odds are, they will not have an injury on the same date as another, unless... it's on the other knee. Perhaps a (TestSubject_subjectId, injuryDate, injuryKnee) composite key would be a solution? Though in all honesty, if you can blow both ACLs in one incident in one day... I doubt we'll be seeing you in our tests :P –  nerdenator Jul 10 '13 at 18:09
    
As far as the SubjectInjuries goes... hm. Interesting point. Maybe have a column that states whether or not the injury occurred within the course of the study. –  nerdenator Jul 10 '13 at 18:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.