Online learning system database

Question

I'm a not-really-recent-anymore graduate of Michigan Tech. I've been a cook since graduation and I'm an eensie teensie bit rusty in pretty much all my learnin'. Couple weeks ago I got hired on to help create an online learning system. Essentially, it's going to be an online school. I need to be able to have departments, majors, classes, students, instructors, grades, etc. ad nauseum.

I know I've got a lot more to go, but before I get much further, I'd like to know if I'm on the right track. I'm really just looking for criticism, best practices, suggestions... praise if it's deserved.

This is the ERD I have constructed:

ERD

This is auto-generated SQL by DBdesigner4:

CREATE TABLE department (
dpt_name VARCHAR(50) NOT NULL AUTO_INCREMENT,
dpt_abbrev VARCHAR(10) NOT NULL,
dpt_desc TEXT NOT NULL,
PRIMARY KEY(dpt_name)
);
CREATE TABLE users (
usr_id INT(8) NOT NULL AUTO_INCREMENT,
usr_lname VARCHAR(50) NOT NULL,
usr_fname VARCHAR(50) NOT NULL,
usr_mname VARCHAR(50) NOT NULL,
usr_primary_email VARCHAR(100) NOT NULL,
usr_reg_date TIMESTAMP NOT NULL,
PRIMARY KEY(usr_id)
);
CREATE TABLE program (
pro_abbrev VARCHAR(8) NOT NULL,
pro_dpt_name VARCHAR(50) NOT NULL,
pro_name VARCHAR(255) NOT NULL,
pro_majormin VARCHAR(50) NOT NULL,
pro_short_desc VARCHAR(255) NULL,
pro_long_desc TEXT NULL,
PRIMARY KEY(pro_abbrev),
INDEX program_FKIndex1(pro_dpt_name)
);
CREATE TABLE hometowninfo (
home_usr_id INT(8) NOT NULL AUTO_INCREMENT,
home_addr1 VARCHAR(100) NULL,
home_addr2 VARCHAR(100) NULL,
home_city VARCHAR(100) NULL,
home_state VARCHAR(100) NULL,
home_zip INT(15) NULL,
home_country VARCHAR(100) NULL,
PRIMARY KEY(home_usr_id),
INDEX hometowninfo_FKIndex1(home_usr_id)
);
CREATE TABLE contact (
con_usr_id INT(8) NOT NULL AUTO_INCREMENT,
con_second_email VARCHAR(100) NULL,
con_phone1 VARCHAR(20) NULL,
con_phone2 VARCHAR(20) NULL,
con_local_addr1 VARCHAR(100) NULL,
con_local_addr2 VARCHAR(100) NULL,
con_local_city VARCHAR(100) NULL,
con_local_state VARCHAR(100) NULL,
con_local_zip VARCHAR(15) NULL,
PRIMARY KEY(con_usr_id),
INDEX contact_FKIndex1(con_usr_id)
);
CREATE TABLE course (
co_pro_abbrev VARCHAR(8) NOT NULL,
co_coursenumber INT(4) NOT NULL,
co_desc_short VARCHAR(255) NULL,
co_desc_long TEXT NULL,
co_days VARCHAR(15) NOT NULL,
co_semester VARCHAR(6) NOT NULL,
co_start_date DATE NOT NULL,
co_stop_date DATE NOT NULL,
co_start_time TIME NOT NULL,
co_stop_time TIME NOT NULL,
PRIMARY KEY(co_pro_abbrev, co_coursenumber),
INDEX course_FKIndex1(co_pro_abbrev)
);
CREATE TABLE student (
st_usr_id INT(8) NOT NULL,
st_co_coursenumber INT(4) NOT NULL,
st_co_pro_abbrev VARCHAR(8) NOT NULL,
PRIMARY KEY(st_usr_id, st_co_coursenumber, st_co_pro_abbrev),
INDEX users_has_course_FKIndex1(st_usr_id),
INDEX users_has_course_FKIndex2(st_co_pro_abbrev, st_co_coursenumber)
);
CREATE TABLE instructor (
ins_co_coursenumber INT(4) NOT NULL,
ins_usr_id INT(8) NOT NULL,
ins_pro_abbrev VARCHAR(8) NOT NULL,
PRIMARY KEY(ins_co_coursenumber, ins_usr_id, ins_pro_abbrev),
INDEX users_has_course_FKIndex1(ins_usr_id),
INDEX users_has_course_FKIndex2(ins_pro_abbrev, ins_co_coursenumber),
INDEX instructor_FKIndex3(ins_pro_abbrev)
);
CREATE TABLE coursefile (
file_co_coursenumber INT(4) NOT NULL,
file_pro_abbrev VARCHAR(8) NOT NULL,
file_fname VARCHAR(255) NOT NULL,
file_type VARCHAR(15) NOT NULL,
file_size VARCHAR(45) NOT NULL,
file_content LONGBLOB NOT NULL,
file_extension VARCHAR(10) NOT NULL,
PRIMARY KEY(file_co_coursenumber, file_pro_abbrev),
INDEX coursefile_FKIndex2(file_pro_abbrev, file_co_coursenumber)
);
CREATE TABLE assignment (
as_id INT(8) NOT NULL AUTO_INCREMENT,
as_pro_abbrev VARCHAR(8) NOT NULL,
as_co_coursenumber INT(4) NOT NULL,
as_name VARCHAR(50) NOT NULL,
as_datecreated DATE NOT NULL,
as_timecreated TIME NOT NULL,
as_desc TEXT NULL,
as_instructions INTEGER UNSIGNED NULL,
PRIMARY KEY(as_id, as_pro_abbrev, as_co_coursenumber),
INDEX assignment_FKIndex1(as_pro_abbrev, as_co_coursenumber)
);
CREATE TABLE assignmentfile (
asgf_pro_abbrev VARCHAR(8) NOT NULL,
asgf_co_coursenumber INT(4) NOT NULL,
asgf_as_id INT(8) NOT NULL,
PRIMARY KEY(asgf_pro_abbrev, asgf_co_coursenumber, asgf_as_id),
INDEX assignment_has_coursefile_FKIndex1(asgf_as_id, asgf_pro_abbrev, asgf_co_coursenumber),
INDEX assignment_has_coursefile_FKIndex2(asgf_co_coursenumber, asgf_pro_abbrev)
);
CREATE TABLE assigned (
asg_pro_abbrev VARCHAR(8) NOT NULL,
asg_co_coursenumber INT(4) NOT NULL,
vsg_usr_id INT(8) NOT NULL,
asg_as_id INT(8) NOT NULL,
asg_date_due DATE NULL,
asg_time_due TIME NULL,
asg_points_earned INT(5) NULL,
asg_comment TEXT NULL,
asg_submit_date DATE NULL,
asg_submit_time TIME NULL,
asg_attempt INTEGER UNSIGNED NULL,
PRIMARY KEY(asg_pro_abbrev, asg_co_coursenumber, asg_usr_id, asg_as_id),
INDEX student_has_assignment_FKIndex1(asg_usr_id, asg_co_coursenumber, asg_pro_abbrev),
INDEX student_has_assignment_FKIndex2(asg_as_id, asg_pro_abbrev, asg_co_coursenumber)
);

Answer 1

This is pretty minor but I wonder why Contact, HometownInfo are seperate tables. If you have that information, it is assosiated with a single user via usr_id, so it may as well be in the same table as the other data assosiated with that user. Otherwise you risk orphan data in those tables after a user is deleted, that sort of thing.

I think you should be making more use of Enums, for things like Semester, because that way you can guarantee the data is in some a standard form when you need to search by semester.

Personally, and some may disagree, I don't like storing files as blobs in the database, rather I prefer to store them as randomly named files in a directory, with a reference to the file in the database. This keeps the DB smaller, and allows staff to upload really big files (with videos in) without trouble...

Also, good luck!

• OverlordSquishy: Yeah, the contact and hometowninfo tables can be combined. I was going to do something convoluted and unnecessary to deal with vagueness on the client's part, but I should really get clearer directions before continuing that part. I totally forgot about adding the enums! Thanks for reminding me. And I'll look into your file storage method. I really just wanted some columns to fill out that table. I haven't yet decided how files will be stored. Thanks for your advice.