Shall I add one or count to get booked number from DB?

Question

I have the following db for a fitness club calendar and booking system. I get omc_courses details which joined to omc_date,omc_week etc.

Now I also want to display total booked numbers for each course.

I thought two ways to do it and I'd like to know your opinions which way is better or if there are any better ways to do it.

My method one.

Adding and deleting one to/from omc_courses.booked whenever a member booked or cancel the booking.

My method two.

Count omc_bookings by course_id, grouped_by course_id and add this number to omc_courses.booked.

Thanks in advance.

CREATE TABLE IF NOT EXISTS `omc_bookings` (
  `booking_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `date_enroll` date NOT NULL,
  PRIMARY KEY (`booking_id`)
) .... ;

    CREATE TABLE IF NOT EXISTS `omc_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_id` int(10) DEFAULT NULL,
  `time` time NOT NULL,
  `course_name` varchar(255) DEFAULT NULL,
  `trainer_id` int(11) DEFAULT NULL,
  `desc` varchar(255) DEFAULT NULL,
  `capacity` int(11) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `order` int(11) DEFAULT NULL,
  `booked` int(5) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ..... ;

  there are more like omc_date, omc_week etc.

Answer 1

I'd do both, because it allows one to act as a verification for the other -- if the two don't match then you've got a bug somewhere. But if you've only got one you may never even spot the bug.

Answer 2

i would use count - why store a value that can be calculated so easy? (and with the stored value you're exposed to danger to forget to increase/decrease you number anywhere, so that the stored number isn't correct at all)

Answer 3

I would not store data in the database that I can get with a query unless in soem very specific cases maybe. In your case you do not need to store booked number anywhere. Just run the COUNT query when you need it.

Answer 4

If you can get the information in a query, then don't bother adding it as a value in another table, this takes your database structure from being a true relational database.

Your current set-up is fine, where you have a table for courses and a table for bookings. So just do a COUNT query using the course ID and time in your WHERE clause to filter.

Answer 5

answer by yourself: do you need the information in the database, permanently, every second the db is running (then store it in the db) or do you need it only in one (or two) specific situations (then perform a count on your result)?