[56K WARN] Database design advice/feedback requested
Posted: Thu May 10, 2007 5:20 pm
I'm looking to create a mysql/php solution for maintaining, searching, displaying, and printing booklists for a school. As I'm not a coder by training or profession, and everything I've done so far with mysql and php has been relatively simple, I'm hoping to get a bit of advice on the database design before I get too far to ensure that I don't paint myself into a corner...
Ultimately, all the information I'll need will include the following fields:
I'd also like the people who have to maintain the book list to be able to also maintain a course list, and an instructor list. The way I'm envisioning it is that they would have a web-based interface where they would first choose whether they need to add a quarter, a course, an instructor, or a book. Then they would have a page that would have pull-downs populated by data from "parent" tables.
For example, if they want to add a new course they would have to choose which year & quarter the course is being taught in, which program the course belongs to, and who the instructor is... then they could input the course number and course name. (They should also be able to choose from a list of existing courses, in case it's a course already in the db, but being offered for an additional quarter. The same would hold true for the booklist - they should be able to "duplicate" an existing book entry for a new quarter.)
So, I'm thinking I should divide this into about five tables:
Quarter
qtr_year
qtr_quarter
Program
prg_programcode
prg_programname
prg_programtype
Instructors
inst_name
Courses
crs_program
crs_coursecode
crs_coursename
crs_clinical
Booklist
bk_year
bk_quarter
bk_programname
bk_coursename
bk_booktitle
bk_authors
Here's a visual of what I've got so far:
Does this make sense?
Is there a better way to go about this?
Thanks,
Scott
Ultimately, all the information I'll need will include the following fields:
- year
quarter
program code
program name
program type
instructor name
course code
course name
clinical (y/n)
book title
book author(s)
I'd also like the people who have to maintain the book list to be able to also maintain a course list, and an instructor list. The way I'm envisioning it is that they would have a web-based interface where they would first choose whether they need to add a quarter, a course, an instructor, or a book. Then they would have a page that would have pull-downs populated by data from "parent" tables.
For example, if they want to add a new course they would have to choose which year & quarter the course is being taught in, which program the course belongs to, and who the instructor is... then they could input the course number and course name. (They should also be able to choose from a list of existing courses, in case it's a course already in the db, but being offered for an additional quarter. The same would hold true for the booklist - they should be able to "duplicate" an existing book entry for a new quarter.)
So, I'm thinking I should divide this into about five tables:
Quarter
qtr_year
qtr_quarter
Program
prg_programcode
prg_programname
prg_programtype
Instructors
inst_name
Courses
crs_program
crs_coursecode
crs_coursename
crs_clinical
Booklist
bk_year
bk_quarter
bk_programname
bk_coursename
bk_booktitle
bk_authors
Here's a visual of what I've got so far:

Does this make sense?
Is there a better way to go about this?
Thanks,
Scott






