Trainings

[bandidom9]

I have to create a database that is suppose to take track of trainings, the student that take the trainings and the vendors(profesors). The same training can happen several times y a year but not at the same time. I want to know if the relationship for the tables that a have so far are ok or is there something im doing wrong that to bring trouble in the future?

[John_G]

Hi -

The way you have this laid out is almost sure to lead to problems. You have too many defined relationships, and some that are wrong.

Your three basic tables are Vendors, Students and Training - those are fine.

I see no use for the VendorTraining table, unless there is something else you did not show. I think you can delete it

You have too many fields in the keys of the tables; you really only need one. Since you are using ID fields, use those as the keys. You can always guard against accidental duplication by using unique indexes where you need to.

Here are some of the things that you could do to fix things up (This is the way I would do it - there may be others)

1) In the TrainingSections table, what is the difference between ID and TrainingSection? It is an important distinction to make, because it determines which field is used in relationship b) below. I am going to assume that ID is the one you want, but you should rename ID to something more meaningful - maybe TrainingSectionID.

2) Take a look at the field TrainingFY. You have it in three tables, but it should only be in one. Since the Fiscal Year applies to when training was given, it is actually a property of a training session - so it belongs in TrainingSections. Delete it from StudentTraining.

3) The relationship between TrainingSections and StudentTraining is going to be on the ID field; you need to add ID to StudentTraining, and delete the TrainingID field from StudentTraining.

4) A Training Session is given by a specific Vendor - to identify the vendor, add the field VendorID to TrainingSections.


Let's look at the relationships:

a) one student can take many training sessions, so Students to StudentTraining is 1 to many on StudentID (as you have shown)
b) when students takes training, they enrol in a Trainingsection; one training section (call it a class) has many students in it, so TrainingSections to StudentTraining is one to many on ID. (you need to define that one)
c) The one-to-many relationship you have between Training and StudentTraining is wrong (delete that one); one type of training can be given many times (TrainingSections), so the proper relationship is one-to-many between Training and TrainingSections on TrainingID.
d) A TrainingSection is given by one vendor, but one vendor can give many training sessions over time, so the relationship is one-to-many between Vendor and Trainingsections on VendorID (you need to define that one)

This is what I would do as as a start - there be other equally valid arrangements.

HTH, and post back if you need more assistance.


John

[bandidom9]

I could not agree more. make so much more sence. Thanks! so it supose to look like this? deleting TrainingFY and TrainingSection from StudentTraining

[John_G]

Now you have it. Don't forget to delete TrainingFY and TrainingSection from StudentTraining. I'm sure you will add more fields and tables as you go; just be sure to avoid redundant data, and to make sure the relationships defined in your database match what happens in the "real world" that the database represents.

Good luck!

John