Database design

Specification

• user information: username, password. Input method--web-based
• project information: project name, description. Input method--web-based
• Data files from Wfccm to be used: Clinical, Scores, Winnerlist, Bins, FDA, Avg Curves, Intensities

Design

• tables, fields, datatype, relationship
• Storage method: Black box: storing the flat files inside of the data base and allowing PHP to parse them for visualization...
• Tables Candidates:
  • Project:
    • Category/Grouping:
      • Comparison:
        • Box Plot
        • Score:
          • Winners
        • FDA Bins
    • Bin:
      • Intensity:
        • grouping file
    • Average

• Details:
  • projectT
    • projectID
    • projectTitle
    • projectDescription
    • projectDeleted
    • projectCreatedDate
    • projectUpdatedDate
  • userT
    • userID
    • username
    • password
    • deleted
  • projectUserR
    • projectUserID
    • projectID
    • userID
  • averageCurveT
    • averageCurveID
    • projectID
    • categoryValueID
    • averageCurveTitle
    • averageCurve
    • deleted
  • binSetT
    • binSetID
    • projectID
    • binSetTitle
    • binSet
    • deleted
  • intensitySetT
    • intensitySetID
    • binSetID
    • intensitySetTitle
    • intensitySet
    • deleted
  • comparisionT
    • comparisionID
    • comparisionTitle
    • deleted
  • scoreSetT
    • scoreSetID
    • comparisonID
    • scoreSet
    • scoreSetTitle
    • binSetID
    • intensitySetID
    • deleted
  • winnerListT
    • winnderListID
    • scoreSetID
    • winnderList
    • deleted
  • (no use)categoryT
    • categoryID
    • projectID
    • categoryTitle
    • deleted
  • (no use)categoryValueT
    • categoryValueID
    • categoryID
    • categoryValue
    • deleted

• Final sql script:
  • create table projectT
    • projectID INT UNSIGNED AUTO_INCREMENT primary key,
    • projectTitle varchar(50),
    • projectDescription varchar(100),
    • projectCreatedDate date,
    • projectUpdateddate date,
    • projectEmail varchar(50),
    • deleted int(1)
  • create table userT
    • userID INT UNSIGNED AUTO_INCREMENT primary key,
    • userName varchar(50),
    • userPassword varchar(20),
    • userEmail varchar(50),
    • deleted int(1)
  • create table projectUserR
    • projectUserID INT UNSIGNED AUTO_INCREMENT primary key,
    • projectID INT UNSIGNED not null,
    • userID INT UNSIGNED not null,
    • deleted int(1)
  • create table averageCurveT
    • averageCurveID INT UNSIGNED AUTO_INCREMENT primary key,
    • projectID INT UNSIGNED not null,
    • averageCurveTitle varchar(50),
    • averageCurve MEDIUMTEXT,
    • deleted int(1)
  • create table binSetT
    • binSetID INT UNSIGNED AUTO_INCREMENT primary key,
    • projectID INT UNSIGNED not null,
    • binSetTitle varchar(50),
    • binSet MEDIUMTEXT,
    • deleted int(1)
  • create table intensitySetT
    • intensitySetID INT UNSIGNED AUTO_INCREMENT primary key,
    • projectID INT UNSIGNED not null,
    • binSetID INT UNSIGNED,
    • intensitySetTitle varchar(50),
    • intensitySet MEDIUMTEXT,
    • deleted int(1)
  • create table comparisionT
    • comparisionID INT UNSIGNED AUTO_INCREMENT primary key,
    • projectID INT UNSIGNED not null,
    • comparisionTitle varchar(50),
    • deleted int(1)
  • create table scoreSetT
    • scoreSetID INT UNSIGNED AUTO_INCREMENT primary key,
    • comparisionID INT UNSIGNED not null,
    • scoreSetTitle varchar(50),
    • scoreSet MEDIUMTEXT,
    • deleted int(1)
  • create table winnerListT
    • winnerListID INT UNSIGNED AUTO_INCREMENT primary key,
    • scoreSetID INT UNSIGNED not null,
    • criteriaSetID INT UNSIGNED not null,
    • winnerListTitle varchar(50),
    • winnerList MEDIUMTEXT,
    • deleted int(1)
  • create table criteriaSetT
    • criteriaSetID INT UNSIGNED AUTO_INCREMENT primary key,
    • criteriaSetTitle varchar(50),
    • criteriaSet MEDIUMTEXT,
    • deleted int(1)
  • create table boxPlotT
    • boxPlotID INT UNSIGNED AUTO_INCREMENT primary key,
    • boxPlotTitle varchar(50),
    • comparisionID INT UNSIGNED,
    • boxPlot MEDIUMTEXT,
    • outLier MEDIUMTEXT,
    • deleted int(1)

Implementation

• text file of sql for easily-loaded