Database design for courses enrollment web site

Hi Experts

I Built a webpage which basically consist on two tables. The purpose is to allow users to sign up for a certain date in a training.With the following table design the page works ,  but as the application has been evolving I been asked to expand to implement new features  Ability to add courses on demand. Different courses right now the course is only one, and each different date is a new row. (I guess this needs to change to allow multiple courses created)

Current I have two tables
Table jos_jquarks_persontraining
id (pk)
user_id
dept
training-Id

Table training
training_id
training
trainingDate
location
TotalSeats
openSeats



Any help that you could provide would be greatly appreciated.
LVL 9
TonyRebaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
Sounds like you want a third table for courses and then what I call a "binding" table that contains keys from all three.  Let's say you add this table:
-- Just guessing at what this would look like
CREATE TABLE courses (
  course_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  course_description TEXT,
  start_date DATE,
  end_date DATE
);

Open in new window

Then you create a *forth* table to relate them all together (and remove training_id from your jos_jquarks_persontraining table):
CREATE TABLE user_training_course (
    user_id INT UNSIGNED NOT NULL,
    training_id INT UNSIGNED NOT NULL,
    course_id INT UNSIGNED NOT NULL,
    UNIQUE user_training_course (user_id, training_id, course_id)
);

Open in new window

The compound index on the end ensures that only one user can sign up for training within one course.  If you want to prevent users for signing up for training *twice* within the same course, you should change this table to (add in a second constraining key):
CREATE TABLE user_training_course (
    user_id INT UNSIGNED NOT NULL,
    training_id INT UNSIGNED NOT NULL,
    course_id INT UNSIGNED NOT NULL,
    UNIQUE user_training_course (user_id, training_id, course_id),
    UNIQUE user_training (user_id, training_id)
);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TonyRebaAuthor Commented:
do I need the 4 tables?



Please see the sample tables and the data on them, what I was thinking is probably a third one with the actual course date and times. But I don't think that would work.  Thanks in advance.
tablesSample.doc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.