You are here:
Vanderbilt Biostatistics Wiki
>
Main Web
>
Projects
>
MicroArrayMassSpec
>
WfccmNotes
>
WfccmWebVisualTool
>
DatabaseDesign
(29 Nov 2005,
HaojieWu
)
E
dit
A
ttach
Database design
Specification
Design
Implementation
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
E
dit
|
A
ttach
|
P
rint version
|
H
istory
: r9
<
r8
<
r7
<
r6
|
B
acklinks
|
V
iew wiki text
|
Edit
w
iki text
|
M
ore topic actions
Topic revision: r9 - 29 Nov 2005,
HaojieWu
Main
Department Home Page
Biostatistics Graduate Program
Vanderbilt University Medical Center
Main Web
Main Web Home
Search
Recent Changes
Changes
Topic list
Biostatistics Webs
Archive
Main
Sandbox
System
Register
|
Log In
Copyright © 2013-2022 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