[56K WARN] Database design advice/feedback requested

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

[56K WARN] Database design advice/feedback requested

Post by Sinemacula »

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:
  • year
    quarter
    program code
    program name
    program type
    instructor name
    course code
    course name
    clinical (y/n)
    book title
    book author(s)
I'd like to have it set up so that the book list can be searched and displayed or printed by "program name," "instructor name," "quarter (year & quarter)," "clinical," or "course name."

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: Image

Does this make sense?

Is there a better way to go about this?

Thanks,
Scott
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

You've done a rather good initial job of analyzing the system that you want to model. You'll need to identify your foreign keys (such as bk_coursename, which must link to id in the courses table) and I'd recommend you follow conventions when naming fields in tables (like, NO spaces, and preface each "id" field with an abbreviation of the table which contains it). Once you have established your primary and foreign keys, it would help for you to draw ER (Entity-Relationship) diagrams, showing which foreign keys reference which primary keys. There are lots of tutorials on ER diagrams on the web (just use the search terms er diagram in Google, for some guidance). I noticed that you don't seem to reference the Instructor name anywhere, perhaps because you haven't tried to be exhaustively complete here. I would assume the foreign key would appear in the Courses table.

When you have done the above, feel welcome to come back here for other suggestions.

However, there will certainly need to be quite a bit of PHP coding in order to develop a working application. It may be a big time consumer for you, if you're not experienced in doing this.

I noticed that you're in San Jose; are you aware that there's a monthly MySQL Meetup group that meets at Google headquarters in Mt. View the second Monday evening of each month? You might want to check out: http://mysql.meetup.com/101/
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Thanks, califdon.

I've done some more reading, and some more thinking (sometimes going in circles, I'm afraid :oops: :lol: )

I've been through several iterations now, and I'm not sure if I'm ending up making it more complicated than it needs to be; but, because there are a few many-to-many relationships (e.g., there are "many" programs in a given quarter, and each program will be available in "many" quarters, and so on), I've moved towards more tables.

Here's a very simple (and probably not technically completely accurate) ER diagram:
Image

And here's the progression of my thinking regarding the database design and the relationships between the tables:
This is more or less where I started:
Image

Then I realized I could link back to information in other tables using the id:
Image

Then I took that one step further:
Image

Then I realized I could divide it up further to make the "one book, many courses" situation easier:
Image

And finally, I could also make the "one course, many quarters/programs/instructors" situation easier:
Image

Does that make it too complicated? (It will certainly make the mysql queries more complicated -- so I'm sure I'll be back for help with those.) Or is it best to get each table as simple as possible and then link the tables together as needed in the queries?

Thanks,
Scott

PS. Perhaps I'll look into the meetup sometime... particularly if I'm going to be doing much more database work!
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Here's one more iteration... this one is like the last above, but with the field names fixed up a bit (I realize I didn't need to prefix every field name):

Image
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post by mad_phpq »

sorry, to butt in... but can you tell me what tool you used to create the database schema?
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

mad_phpq wrote:sorry, to butt in... but can you tell me what tool you used to create the database schema?
Sure... it's the trial version of SQLEditor on Mac OS X.

(The first version–in the first post, without relationships, etc.–was done with OmniGraffle.)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Sorry, I didn't get back to the forum for a couple of days. You've made a lot of progress. I don't have time right now to really try to analyze your schema, but your progression has been in the right direction, and is exactly what you have to do, to get it right, which is crucial to the rest of your application development. All too often, people don't invest enough time up-front, spend a lot of time coding, only to realize that they have to start all over again because they didn't have the schema right, to begin with.

No, that doesn't look too complicated (although I haven't really thought about it enough to say that it's the correct relationships). The good thing about SQL is that you can express very complex relationships with compact expressions, once you have a solid schema.

I'll be at the MySQL Meetup in Mt. View this evening. I'll try to get back to your schema tomorrow.

Don
Post Reply