Database logistics

ssyed

Hi, I'm wondering what the best way to organize large amounts of data is.
Say I am building a course catalog and I want reviews of classes etc. Is it a bad idea to do something like this:

Database : Course Reviews
Tables:
[Course Name] -- fields : UserName, Comments, Professor, difficulty ratings, Stuff to watch out for, homework difficulty/description, exam difficulty/description.

Database : Professor Reviews
[Professor Name] -- fields : UserName, Comments, teaching style, approachability/friendliness, availability, difficulty ratings, Stuff to watch out for, homework difficulty/description, exam difficulty/description, fun/description.

Data base: Course info
Tables
[Course Name] – fields : Quarters offered, Units, difficulty rating, fun rating, description, catalog number, department, Professors, reviews, interactive

There are 3 databases with probably 1000 tables each ... is this good or bad? are there any rules or guidelines in how many tables you should use or how you organize data?

ssyed

also is it bad to have many databases on one server all used for a single site, or is it better to have one BIG database?

bradgrafelman

So wait... for the "Course info" database, you have all of those tables with what... 1 row each? Uh... yeah... definitely overkill on the separation there; I'd say all of the courses should be in one table.

Same with professors and course review. They can be in the same database, one table each. I would add primary, auto_increment "id" fields to each table, too.

In addition, I would make a new table that simply defines professors.

For course reviews, instead of having the course name, you could have a referring course_id (or even a foreign key, if you're using InnoDB tables) that links the reviews with a row in the course info table. Same for the professor column - instead of having the professor's name, you could have the prof_id that links to the corresponding row in the professors table.

Though I'm no DB guru, it seems like you need to do some combining as well as some database normalization here. You asked about a "rule" for number of tables. I can't comment on that... other than saying this: if you find that there is duplicate data, e.g. two different places where you see a professor's name, then there's probably still some room for normalization (making a separate table to house the professor's name, linking to the corresponding row using an id).

Sxooter

Yeah, you're denormalizing your design. Basically, you should not need to add / remove tables in the normal course of operation of a db oriented app (unless the app is a db design app, but that's another story.)

You should have a table for courses, a table for professors, a table for reviews of professors, etc...