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
Topic revision: r9 - 29 Nov 2005, HaojieWu
 

This site is powered by FoswikiCopyright © 2013-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Vanderbilt Biostatistics Wiki? Send feedback