3
\$\begingroup\$

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)
);
\$\endgroup\$
1
\$\begingroup\$

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!

\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$ Apr 10 '12 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.