DB design advice.

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

I have been working on a module for a project at work and need advice on the structure of my database.

Let me explain.

I work at Wright State University in the School of Medicine. Each year we take in about 105 new students. Each student basically has the same classes all year long, it is very structured.

All Y1 students have Team Learning (TL) "sessions" in almost all of their classes. A TL session is three different grades all on a single day. First they come to class for example Principles of Disease (POD), they take a test called an Individual Readiness Assessment test (IRAT), then later take a Group Readiness Assessment test (GRAT), then finally take a Application Excersice (AppEx). The GRAT and IRAT are "group" scores. The IRAT is based on a individual score.

My current db structure is this:

Code:

# --------------------------------------------------------

#
# Table structure for table `atlas_course_directors`
#

`Course_ID` varchar(25) NOT NULL default '',
`Director_ID` varchar(25) NOT NULL default '',
PRIMARY KEY (`Course_ID`,`Director_ID`)

# --------------------------------------------------------

#
# Table structure for table `atlas_courses`
#

`Course_ID` varchar(25) NOT NULL default '0',
`Course_Name` varchar(255) NOT NULL default '',
`Course_Number` mediumint(6) NOT NULL default '0',
`Course_Year` set('1','2') NOT NULL default '1',
`Calendar_Year` varchar(5) NOT NULL default '0000',
`Department` varchar(255) NOT NULL default '',
`multipart` enum('yes','no') NOT NULL default 'no',
`Director` varchar(30) NOT NULL default '',
PRIMARY KEY (`Course_ID`)

# --------------------------------------------------------

#
# Table structure for table `atlas_directors`
#

`Director_ID` varchar(25) NOT NULL default '',
`LDAP_USER` varchar(30) NOT NULL default '',
`Name_Last` varchar(30) NOT NULL default '',
`Name_First` varchar(30) NOT NULL default '',
`Email_Addr` varchar(50) default NULL,
`status` enum('active','inactive') NOT NULL default 'active',
PRIMARY KEY (`Director_ID`),
UNIQUE KEY `LDAP_USER` (`LDAP_USER`)

# --------------------------------------------------------

#
# Table structure for table `atlas_tl_group_students`
#

`gid` int(11) NOT NULL default '0',
`SOMS_KEY` int(11) NOT NULL default '0',
`LDAP_USER` varchar(255) NOT NULL default ''

# --------------------------------------------------------

#
# Table structure for table `atlas_tl_groups`
#

`gid` int(11) NOT NULL auto_increment,
`gname` varchar(32) NOT NULL default '',
`gdesc` text NOT NULL,
`glimit` int(11) NOT NULL default '0',
`Group_Year` tinyint(4) default '0',
PRIMARY KEY (`gid`)

# --------------------------------------------------------

#
# Table structure for table `atlas_tl_session`
#

`Session_ID` int(11) NOT NULL auto_increment,
`Course_ID` varchar(25) NOT NULL default '',
`Director_ID` varchar(25) NOT NULL default '',
`Session_Name` varchar(50) NOT NULL default '',
`TLDate` date NOT NULL default '0000-00-00',
`status` enum('1','2') NOT NULL default '1',
`Calendar_Year` varchar(5) NOT NULL default '',
PRIMARY KEY (`Session_ID`)

# --------------------------------------------------------

#
# Table structure for table `atlas_tl_students`
#

`Class_Year` tinyint(4) default NULL,
`Cohort` year(4) default NULL,
`Email` varchar(30) default NULL,
`Graduation_Year` year(4) NOT NULL default '0000',
`Name_First` text NOT NULL,
`Name_Last` text NOT NULL,
`Name_Middle` text,
`Start_Year` year(4) NOT NULL default '0000',
`SOMS_KEY` int(11) NOT NULL default '0',
`SPRIDEN_PIDM` varchar(25) NOT NULL default '',
`U_Account` varchar(9) NOT NULL default '',
`LDAP_USER` varchar(255) NOT NULL default '',
PRIMARY KEY (`SOMS_KEY`),
UNIQUE KEY `GOBTPAC_LDAP_USER` (`LDAP_USER`)

After adding student to groups I think the group grades I can figure out by adding groups to each "session" or by adding groups to each course and just kinda "autopopulate" each session, but what would be the best way to record the IRAT grades? The IRAT grade is individual and not part of any group grade.

This whole project is a way to get away from the multiple spreadsheets used, and make a Team Learning application for course directors to track their TL efforts. The Dean of the School is a big advocate of the Team Learning "collaborative" effort.

kguske · Site Admin Joined: Jun 04, 2004 Posts: 4887

Another option:
`Session_ID` int(11) NOT NULL,
`gid` int(11) NOT NULL default '0',
`SOMS_KEY` int(11) NOT NULL default '0',
grade type (IRAT, GRAT, AppEx, etc.)
grade
primary key: session_id, gid, soms_key, grade type

That way, a student's grades can be determined from one table. You could either insert a record for each student in the group, or leave SOMS_KEY null for the group grades.

There are a couple of issues, too. One, is it possible for a director to have more than one of the same course in the same session? If so, you'll need an additional key on that table (or to make your application define it differently).

Also, by storing the student ID (soms_key) in the grades table, you'll be able to account for changes to the group composition (i.e. if a student drops the course, etc. before the grade is given, you won't have to remove him from the group - just make inactive so no grade is written for inactive students when the group receives a grade.

Obviously, for the IRAT grades and other individual grades, there would be no gid.

Finally, when the next dean places lesser value on the team learning collaborative effort, you simply don't add groups or grade at the group level and no application changes are required... Smile

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

So then this would be the new atlas_tl_session table?

If the grade field needed to contain a grade %, what data type would you use.

For example if they made a 86% on the GRAT?

kguske · Site Admin Joined: Jun 04, 2004 Posts: 4887

It would be a grades table, associated to the session table. I'd probably use an integer for the grade field. But if it's possible for any course / test to have a letter grade, too, you'd have to use character / string.