Mysql - A simple database design question

Question

Suppose I have Tutors who take online webclasses and create learning packs. Both online webclasses and learning packscan be rated by students and finally a tutor's rating is the simple average of all the ratings on his classes and packs.

This is the table architecture of our current Ratings table -

CREATE TABLE IF NOT EXISTS `Ratings` (
  `id_rating` int(10) unsigned NOT NULL auto_increment,
  `id_teacher` int(10) unsigned default NULL COMMENT 'the teacher who created the class/pack',
  `id_lp` int(10) unsigned default NULL  COMMENT 'the id of the learning pack',
  `id_wc` int(10) NOT NULL default '0' COMMENT 'the id of the webclass',
  `id_user` int(10) unsigned NOT NULL default '0' COMMENT 'the user who has rated',
  `rate` int(10) unsigned NOT NULL default '0',
  `cdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `udate` timestamp NULL default NULL,
  PRIMARY KEY  (`id_rating`),
  KEY `Ratings_FKIndex1` (`id_user`),
  KEY `id_lp` (`id_lp`),
  KEY `id_wc` (`id_wc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Currently, both class and pack ratings are entered into the same table (id_wc and id_lp are entered accordingly - one is entered and the other is NULL for every rating record).

So, my question is -
Is this architecture correct or is it better to keep class and pack ratings separate? Why or why not? I need exactly the same number of Rating table fields for both class and pack rating.

I guess, If class and pack ratings were to be separatelt found, then separate tables would reduce the number of records to be looked up. But, since in our case only tutor ratings are needed (involves both class and packs), that's why all the ratings are put together.

Answer 1

If you think that you'll end up with more entities which will require rating then you need to create something more generic (and not very db-philosophy-friendly).

ratings
-------
id
voterClass
voterId
subjectClass
subjectId
vote
date(s)

By using this design you forget about FKs and referential integrity. But it's very flexible, and using the right indexes it's very scalable. Also, when entities (subjects) are deleted the votes remain. This design saves you from duplicating fields and tables.

Answer 2

A bit more detailed model.

• A teacher at a university can take classes too.
• One class may have more than one teacher.
• There may be several classes on the same subject, taught by different teachers.
• Only students who participate in classes get to vote (rate) the class.
• Learning packs are on a subject (math, biology).
• One learning pack can have several authors.
• Technically, a student can author a learning pack too.
• Only members who use a learning pack get to rate a pack.
• Although authors can vote for packs and teachers can vote for their classes, those votes are ignored.

If only interested in the ratings table, you could use:

Or, combine both models into something like: