Adding Tables to Database

[KristenD]

I have completed the first part of my database, mostly thanks to the extremely knowledgeable experts and their very helpful advice.

I am now ready to move on to the second part of the database. The second part should be fairly easy as we are just tracking employee trainings and licenses.

I will be adding tables but I'm not sure if I should just add one BIG table or split them up. There will be a lot of blank spots in the tables as not all employees will have all trainings and only certain employees will hold certain licenses due to where the job is.

I am leaning towards splitting it up into many tables as for the state licensing ther are 2 states that require those trades to be licensed in that particular state, and depending on the county/city there may be additional licensing requirements for those trades.

There are 28 "Other" trainings that are NOT a requirement but certainly sometimes needed depending on the type of job.

There is one OSHA training that will be it's own separate table.

They will all be tied back to the EmployeeID. They should all have a one to one relationship correct? Also, and I maybe getting ahead of myself, some of the trainings expire so we want to be able to track that with reports.

The more I think about it, each training should have it's own table, Correct?

Thanks again for all of your help and advice!

 

[KristenD]

mostly thanks to the extremely knowledgeable experts and their very helpful advice.

From this form...I forgot to clarify!

 

[Bryan]

It seems to me that you should add TWO tables . . . one for licensing and one for training. This would provide for one to many relationships between the employee and their licenses and the employee and their training.

I am not sure what advantage there is in putting each training into separate tables. I'm guessing that you may need to query an employee's licensing or training taken. Having all of the training in one table, for instance, makes for a fairly simple query.

 

[KristenD]

Thank you...that was my original thought when they wanted me to add the info to the database but I just wasn't sure knowing there would be a lot of blank fields.

Totally off track, but where in Jersey are you from? I'm originally from there, still have family down there as well.

 

[Bryan]

You shouldn't have blank fields with a one to many for training. If they took in the training, there should be data for it. If they didn't take the training, there will be no record at all.

I'm sending you a PM re NJ.

 

[RainLover]

Kirsten

Create your tables as best you can.

Then create the Relationships in the Relationship Window. Print it out and stick that on the wall. This is your map for further design.

If the Relationships look wrong they probally are. I would think Training is only ONE table. But that table may have Foreign Keys. Would not an employee receive various types of Training.

So an Employee with more than one qualification in training would make it one to many.

Have a look at the attached. Hope it helps.

 

[KristenD]

Ok, I will create all the tables and post the relationships up here to see if they are correct. Thank you!!

 

[KristenD]

THat did help Thanks!!

 

[RainLover]

New Attachment.

 

[KristenD]

I have figured out the tables on paper. My only concern is the tblTraining has 61 fields and has the potential to add more. Some of the trainings have expiration dates but others do not. I am not sure with the ones with expiration dates if I should show a start and end date. This is the way some fields are set up now:

OSHA 10 Data Type Yes/No
OSHA Date

 

[KristenD]

Sorry it posted somehow early.

OSHA10 Data type Yes/No
OSHA10Date Data type Date
CPRTrained Data Type Yes/NO
CPRTrainDate Data Type Date
CPRExpDate Data Type Date

I have 28 Trainings with 7 of those that have expiration dates. Is this ok to have that many fields in a table?

 

[RainLover]

Kirsten

It is not the number of Fields that matter, it is more along the lines of "Is it correct"

The CPR Dates could be used for all Training types.

E.G.

FirstAid Dates
Driving Dates
WorkingAtHeights Dates.

Am I helping or confusing.

 

[RainLover]

Have a think about this.

Your design (If Correct) should allow for addition items.

If a new Training Type is introduced, can the user add it or does it require the Designer to redesign.

There is a term "Hard Coding". This is where things are set in stone and only an alteration to the code will allow for something new. This is a Bad thing.

Your Database must be able to grow without the designer's intervention.

 

[KristenD]

I would like to be able to add new trainings if needed. I would be the only one adding the trainings and I am the developer. But, that COULD change down the road. As of right now, there is only me doing the entry and the rest of the front ends are used only to print or export reports.

I totaled it up yesterday and there would be a total of 61 fields. But your suggestion would be to just add the fields as dates. So like above it would be:

SignalRiggerDate
CPRDate
CPRExipirationDate
FirstAidDate
FirstAidExpirationDate
ForkliftDate

That would greatly reduce the amount of fields. Is this along the lines of what you were describing?

 

[RainLover]

Not exactly.

I would have a Table.

tblTraining

TrainingPK AutoNumber
TrainingType Text
StartDate as Date
EndDate as Date
Comment as Text

Four Fields plus another one or two to suit your purpose.

But that is all.

Now you can have Thousands of different Types of Training.

 

[Bryan]

I think Rain was suggesting the fields be more generic.

Instead of having both a cprexpirationdate and a firstaidexpirationdate, you should just an ExpirationDate.

I can see fields like:

TngType
TngDateBeg
TngDateEnd
CertRequired
CertDate
CertExpireDate

If you need to maintain a certification history, you'll need to consider if the certification history should be tracked in a seperate table with a one to many relationship with this table.

 

[RainLover]

I think Rain was suggesting the fields be more generic.

Yes Bryan you are on a similar line of thought to me.

I am off to look at the insides of my eye lids.

Good night John Boy.

 

[KristenD]

Ok, I think I understand the table now.

There are only (as of right now) 6 trainings that have expirations. All of the other trainings, for example Signal & Rigger Training, only have one date. There is no expiration. So could I construct the table like so:

tblTraining
TrainingType
TrainingDate
TrainingExpiration
Comment

And then allow a Null value for the TrainingExpiration field?

 

[Alansidman]

Or if you want to require the expiration date, make it so far into the future that all users would know that it never expires. Something like 1/1/2050.

 

[RainLover]

Now to make things more complicated.

Date-Expired is wrong. It should be a Text field called "Valid-For", "Length" or "Period"

You enter the length of time the Training is valid for. Like CPR may be 6 Months.

Next create another Table for Training Renewal which is a Many side to tblTraining. This will give a History of renewed training. e.g. CPR would have 4 entries in two years.

The expiry Date should be a calculated field based upon the date previously attained plus the term namely 6 Months for CPR.

This is the correct method and needs to be used if this is a long term Data Base.