Training Database Design in SQL using C#, sql, asp.net, exchange

For a possible solution using Active Directory and Exchange see my post below.

We would like to create a training database in SQL which we can use for our internal training sessions of our employees. Unfortunately I do not have any experience in database design and did not have a chance to buy and read a proper book about this topic.

I have just started to create a database after reading a few tutorials online and would like you to review my design and provide me with some feedback if I have started more or less correct.

The courses开发者_运维知识库 table will store our training courses with their duration, capacity and a small description of what you will learn on this course. The training session table will be used to link a course with a specific training and a date when the training will be done. The trainers are colleagues who provide the internal courses.

The attendance table stores the training session id and if an employee attended the session or if he could not.

Please find below our database diagram:

alt text http://img8.imageshack.us/img8/2464/trainingdb.jpg

Later on we would also like to store the job position a training course is relevant for. For example our network introduction course is relevant for a Level 1 Analysts, a Level 2 Analysts and Team Leaders. Our ITIL course is relevant only for a team leader.

How would you store this information? Would you use a separate table with the positions and use a many to many relationship for this?

Many thanks,

Mathias

The structure seems fine. I'd suggest adding one more foreign key relationship, though: Attendance.EmployeeID should reference the Employee table.

Attendance doesn't need its own primary key. The combination of employee and session uniquely identifies it (a given employee can't attend a given session more than once, can they?). You should probably use the two ID columns for those as a composite primary key.

Do courses really have a capacity, or is it a session which has a capacity?

What's the UpdateTime column for?

A bit simplified, does not account for enrolment, but may help you with ideas.

Below an explanation of the tables

We use the module category, module type, course, programme, training method and post work tables to categorize the training module using dropdown lists. The relationships are 1:n.

The module <-> employee relationship is m:n. As you can see from the model, the intersection table is Trainer where we define the additional property of Priority to allow us to define trainer priorities for a module.

The training module <-> role relationship is a many-to-many relationship as a module can be relevant to many job roles. The intersection table is RoleRelevance and we define for each role required, recommended, probation and hide properties.

The training request table keeps a record of each training request that has been requested. We also have new starter requests were we do not have a domain profile / SAM we can link the request to.

The employee table is being populated from our domain controllers with AD queries while employees are requesting a training or trainers are being defined for a module. The table includes the employee smtp address used to send invitations. See my other stackoverflow posts for a code sample how to get this data.

We create meeting invitation with managed EWS for the employee, line manager, trainer and resource/room. The invitation id and status (accept/decline/unknown) are stored in the EmployeeInvitation, TrainerInvitation and ResourceInvitation tables.

Training sessions we create are being inserted into the training session table.