Database Design Issues with relationships

Answer 1

What you're looking for is relational division Not implemented directly in SQL, but it can be done. Search google for other examples.

Answer 2

After a quick look at the schema I think one of the things you can do is create a 'to_be_certified' table. Populate it with user_id, product_id and seat_id when a product is assigned to a seat (when product_seat_rtab is populated).

On adding a record to the certification_rtab table, delete the corresponding record in the 'to_be_certified' table. This will give you an easy access to all the products which are certified for a users and the ones that are not.

To get rid of duplicate product_ids, you can group by product_id.

Answer 3

You need to make changes to the lessonstatus_rtab table:

CREATE TABLE lessonstatus_rtab (
  user_id    INT NOT NULL,
  seat_id    INT NOT NULL,
  lesson_id  INT NOT NULL REFERENCES lesson_rtab,
  accessdate TIMESTAMP,
  score      NUMERIC(5,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, seat_id, lesson_id),
  FOREIGN KEY (user_id, seat_id) REFERENCES user_seat_rtab (user_id, seat_id)
);

Then you can query for each product that a user has a seat for, is he certified? This presumes that the number of lessons he has scored, say, 50% or higher is the same as the number of lessons in all modules for the product.

SELECT p.name, us.user_id, us.seat_id, COUNT(l.id) = COUNT(lu.lesson_id) AS is_certified
FROM user_seat_rtab AS us
 JOIN seat_rtab AS s ON (s.id = us.seat_id)
 JOIN product_seat_rtab AS ps ON (ps.seat_id = s.id)
 JOIN product_rtab AS p ON (p.id = ps.product_id)
 JOIN product_module_rtab AS pm ON (pm.product_id = p.id)
 JOIN module_rtab AS m ON (m.id = pm.module_id)
 JOIN module_lesson_rtab AS ml ON (ml.module_id = m.id)
 JOIN lesson_rtab AS l ON (l.id = ml.lesson_id)
 LEFT OUTER JOIN lessonstatus_rtab AS lu 
   ON (lu.lesson_id = l.id AND lu.user_id = us.user_id 
     AND lu.seat_id = us.seat_id AND lu.score > 0.50)
GROUP BY p.id, us.user_id, us.seat_id;

Answer 4

UPDATE:

I have considering this issue further and have considered whether it would allow things to work better to simply remove the user_seat_rtab table and then use the equivalent certification_rtab table (probably renamed) to hold all of the information regarding the status of a user's seat. This way there is a direct relationship established between a User, their Seat, each Product within the Seat, and whether the User has certified for the particular Product and Seat.

So I would apply the following changes to the schema posted with the question:

DROP TABLE user_seat_rtab;
RENAME TABLE certification_rtab TO something_different;

An alternative to further normalize this new structure would be to do something like this:

ALTER TABLE user_seat_rtab 
    DROP PRIMARY KEY;
    ADD COLUMN product_id int(10) unsigned NOT NULL;
    ADD CONSTRAINT pk_user_seat_product PRIMARY KEY (user_id, seat_id, product_id);
    ADD CONSTRAINT fk_product_user_seat FOREIGN KEY (product_id) REFERENCES product_rtab(id) ON DELETE RESTRICT;

I'm not really certain whether this would solve the problem or if it will just change the nature of the problem slightly while introducing new ones. So, does anyone have any other criticisms or suggestions?