Database Schema question

Question

Hello all,

I am having a difficult time deciding how to handle a business requirement in my database schema. I have a lot of tables in the database, but there are only three I need to deal with for this problem: Courses, PersonnelCourses, and Personnel.

• Courses is a list of Courses
• Personel is a list of Personnel
• PersonnelCourses is a list of Courses that Personnel have taken.

In courses there is a column called Universal. If a course is universal, that means all Personnel must take that course.

I need to generate a list of all the universal courses that Personnel must take, but the only way I am able to generate this list is with a cross join / cartesian join:

select P.LastName, C.Name from Courses C, Personnel P where Universal = 1

From that I want to do a left join onto PersonnelCourses so that I can have a list of all the Personnel and the Courses they must take as well as the courses they have taken. I'm thinking this would all be easier if there was a many to many table between Personnel and Courses. But if all Personnel are going to be in this middle table anyway, isn't that a bit redundant?

Is there a better way to handle this?

Much appreciated,

-Matt

Answer 1

There is a list of courses that everybody has to take. Why not just take this list and work with it, instead of repeating the same list for every personnel row? I don't understand why you are trying to multiply your result set.

Answer 2

All personnel will not be in the personnelcourses table, only personnel who have taken courses.

I think you design is fine. You just need to tweak your query to get what your after.

In a subquery pull the courses the personnel have taken. Then in an outer query select all the courses that the personnel must take and do a left outer join with the subquery.

Select a.CourseName, b.PersonName from Courses a, 
(select P.LastName, C.Name from Courses C, Personnel P, PersonnelCourses pc 
  c.courseid = pc.courseid and
  p.personnelid = pc.personnelid and
  c.Universal = 1)  b 
where
a.courseid += b.courseid order by courseid

It would probably be best to filter by personnel, if this is for a report. That way you would see all of the courses required including the ones taken per person.

Answer 3

Isn't your PersonnelCourses establishing a many to many relationship between a Persons and Courses? If it isn't then I am not sure, If it is then...

select *

from Personnel_Courses 
    inner join Person on... /*get the Person details*/
    inner join Courses on... /*get the Course details*/

where Course.Universal = 1 and Person.Id = @Id

would tell you what universal courses they have taken...

and then

select *

from Courses

where Courses.Universal = 1 and Course.Id not in (
        select Course.Id from Personnel_Courses 
        inner join Person on... /*get the Person details*/
        inner join Courses on... /*get the Course details*/
        where Course.Universal = 1 and Person.Id = @Id
        )

Would give you the universal courses that they haven't taken...

To me it might be easier to do the 2nd in your code (Get the first query results, Do a select from the Course table to get all the universal and then do a comparison...)

Answer 4

This is a topic Database Normalization that books have been written on and part of why you want to do this is DRY or don't repeat yourself.

So to answer your question about a better way - I would answer no.

Answer 5

How about something like this (using the existing structure)?

SELECT P.LastName, C.Name, 1 as Taken  
FROM Courses C   
  INNER JOIN PersonnelCourses PC ON (C.CourseID=PC.CourseID)  
  INNER JOIN Personnel P ON (P.PersonID=PC.PersonID)  
WHERE(C.Universal = 1)  

UNION  

SELECTP.LastName, C.name, 0 as Taken  
FROM Courses C, Personnel P   
WHERE (Universal = 1) and   
   NOT EXISTS(SELECT * FROM Courses C2 
             INNER JOIN PersonnelCourses PC2 ON (C.CourseID=PC.CourseID)
             INNER JOIN Personnel P2 ON (P.PersonID=PC.PersonID)
             WHERE (Universal = 1)  and 
                    (PC2.CourseID=C.CourseID) and       
                    (P2.PersonID=PC2.PersonID)
             )

Answer 6

Universal is a two-value (boolean) attribute of Course, right? In that case, consider normalizing further. Redesign so that UniversalCourse is a table, not a column on Course. That table would have a single column referencing the course. To find all universal courses, simply select everything from this table. Now you can shorten your cartesian join considerably since you have to multiply Personnel only by the UniversalCourse table, having eliminated the where Universal = 1 clause.