Tables correct

[Hope]

Hello,

My last Db the forum was commenting on my table structure so can someone please look at my tables and tell me if they are set up correct so I start off on the right foot. My boss wants me to start creating DB's, I am starting to create a db for him to enter in all the training coarse our employees have taken, which ones they need to take, when the classes are available, if they passed or failed all that info, so I created 3 tables with all the info he wants and they all need to link together so I can create a form and query and pick out certain information.

Any ideas or advise would be great.

Thanks

 

[Hope]

hello

Any thoughts out there on this?

 

[Moniker]

14 views and no comments is either a good sign or a bad sign. But if you're paranoid about it, take what you will from that. For your consideration:

How normalized is it?
How future-proofed is it? (You'll never be entirely future-proof, but will it break with just a minor change?)
What issues were you concerned with in the first place? Why are you asking for a second (or 14th) set of eyes?
What do you think are the weakest and strongest points of your structure?
What concerns you the most? In other words, what part prompted you to post for "confirmation" in the first place?

 

[Hope]

I ask because I thought my last DB was normalized and set up correctly, then when I posted it, the forum kept saying I needed to work on it. I have that db up and working fine now with no problem and maybe it isn't perfect but its doing what i need it to do right now. So I ask this time so I start off on the right foot. I am rusty at all of this and trying to relearn everything.

 

[Moniker]

So what is it you don't get about normalization? What is it you're questioning yourself about?

Very briefly (and an ad hoc definition):

Normalization means that you store repetitive data once (like city names, for example) and then refer to it by something like "city_ID". You can see this in the URL for this post. You can see this in any commercial website. Basically, something that might be stored in more than one table should be normalized, meaning something that is stored in more than one table CANNOT be spelled out, but instead referenced.

A table that contains the word "North America" as a continent more than once (spelled out) is not normalized. The continents should be "normalized". You refer to lookup at that point.

You probably already get all of this. That's all normalization is, though. Don't repeat data that may repeat. Refer to it through an ID of some sort.

Don't go overboard (don't "normalize" zip codes, for example), but at the same time, if you were making an address book DB, the name of the address entry would be normalized, while the address details would not. For example, "John Smith" could be "1" on the MainAdressID field, because it might look like this:

MainAddressID   AddressID   Address
1               1           123 Elm St.
1               2           456 Oak Ln.
1               3           789 Maple Ave.

All of those can be related in reverse, because they all relate to the same MainAddressID. This is referential integrity, pre-built.

If you're not sure on something like my example, then you might be diving into the deep end without floaties. Use the DB relationships to set all this up.

 

[Hope]

Does anyone have a sample training db?

 

[Moniker]

The Northwind DB, included with Acces, is normalized. Here are a few links with good descriptions of what it is and how to do it.

Normalization Overview

The Wikipedia entry (good examples)

Simple poster explaining it (PDF file)

 

[Hope]

ideas

Hello all,

I have to create a db for my boss to train training in the plant. He wants a list of required classes for example a die-setter must taken and he wants to be able to search on an employee and see what classes they have taken and which classes they still need to take. I broke it down to 5 tables

empname-to enter new employees
requiredclasses-which would have the title and the required classes that go with that title
results-employee results from taking the class
title-a list of titles so I can link it to requiredclasses
training-a list of the service providers and there training class

I believe I have the tables broken down pretty good, my question is how to link them together to pull out the information my boss is looking for. I am just playing now so please dont mind the design, after I master the goal of pulling the information I will work on the design.

any advise would be great.

 

[CraigDolphin]

Hi Hope.

At a quick glance I can see some issues and normalization problems.

For example:

1. Your table RequiredClass seems to have repeating fields for Classes. Instead, there should be a table to list Classes (One field for the class name, another for the ClassID autonumber pk).

2. You should be storing the TitleID, not the Title, in the EmpName table.

3. You need a table to list which ClassID's are required for each TitleID (junction table between the Title table and the Classes table). I think that this also makes the ReqOOpt field redundant.

4. You need a table to list actual TrainingEvents. This would link EmpID and ClassID, and show CompletionDate, and some kind of pass/fail result. The closest you have at present is the results table.

5. If you want to track trainer's then you need a table that lists trainers, and store the TrainerID as a foreign key in you class table. If there is any possibility of more tha n one trainer teaching the same class over time, then you may wish to have a TeachingHistory table instead of storing the foreign key directly in the class table. This table would link ClassID with TrainerID, and possibly use a StartTeachingDate field to help you sort out who was teaching what, when.

You may want to look at another thread where this topic was discussed.
http://www.access-programmers.co.uk/forums/showpost.php?p=644216&postcount=6

There are some differences in the example I posted on that thread but the basic structure you need is there, and also some queries to show how to use that structure to produce lists of employees who have or haven't done the required courses. That example did not include information about pass or fail but it could easily be modified to do so.

You could modify or add onto this to include information about trainers etc.

 

[Hope]

CraigDolphin, thank you so much for your help and time that helps alot, I will restructure my tables.


Ok, I restructed and added alot of tables, after looking at what my boss wants there is alot of repeating fields so I had to add tables to normalize it.

 

[CraigDolphin]

Hope,

have a look at the attached file. I've simplified the structure from the other thread (previously it tracked jobs as well as employees in case employees changed jobs within the company)

Now, depending on the situation you may need to change the placement of the ProviderID and CourseCost Fields. If all employees always use the same provider for a particular course, then the current placement is correct. However, if some employees attend course 1 at provider 1, and other employees attend class 1 at provider 2, then you will need to move those fields from the tbl_Courses table to the tbl_EmployeeCourses table instead.

If you look at the example I referenced earlier, you'll note that there's no need for a field to say whether a class is optional or required. If a class is required for a title, then there will a be a record in tbl_CoursesRequired that links that class with that title. If there's no record in that table linking the two, then the class is optional for that position. Have a look at the queries there to see how to make this work for you.

I've also added in a Passed field in the tbl_EmployeeCourses table to record whether the employee passed (yes) or failed (no).

 

[Hope]

db

I want to be able to add a new employee and their title (Add Employee Form)
I want to be able to add a new Title in sytem (new employee title)
I want to be able to add a new catorgory say Safety, then list the classes that fall under safety(Catergory form)
I want to add new service provider and what classes they teach(serviceproviderform)
I want to enter employee and the training class they took and when the completed it(Training Taken tab)
Then I want a search page to answer questions like
Who took this training?
Who provides the training?
What training is due and who needs to be scheduled?
What training events are in each category?
Things like that so I want to build a query to answer all these questions and lots more
But it doesn’t seem to be working and I need some direction. I just went back to your original example and modified from there, hope you don’t mind. It doesn’t seem to link right. I only added two queries to test for now and well as you know not working the way I want them too. Trying to learn this.

 

[CraigDolphin]

Hope, first up...you've removed a table from my original design that is actually quite important. Look at what you have and ask yourself how the database is supposed to know what jobtype an employee has? Tell me anything about the job that an employee has, if you can.

So, let's see how well YOU fare without that information.

Pretend I'm an employee. My JobID is 85.
JobTYPE 1 requires training A and B
JobTYPE 2 requires training A,C, and D
JobType 3 requires training B and E

What training is required for me?

 

[Hope]

db

New queries

 

[CraigDolphin]

Hope,

I feel like I've done about as much as I can to point you in the right direction, even pointing you to an example db with working queries and then simplifying it further to make it easier for you to follow.

But looking at the file you just posted I can't help but thinking you're just not understanding me.

The results of a course/class for an employee should NOT be stored in the Jobs table. Fields in tables should depend entirely on the primary key of that table. A job's pay grade, for example would be stored in a table about jobs. A person's last name should be stored in a table about people/employees. A course result for a person should be stored in a table about couses that people have taken. In this case, tbl_EmployeeCourses. Likewise, course costs depend solely on the course being taken therefore they belong in the Courses table....or, if you want to track costs over time, then you'd want to place this information into a CourseCostHistory table.

And I already covered the issue of the ReqOOpt field that you keep insisting on using and explained WHY it is NOT necessary but I see you've brought it back in yet again.

You are also confusing something else of major importance. In the original example I pointed you to there were two fields in the jobs table. One was PositionTitleID and the other was JobTypeID. In that example, training requirements were based on JobTypeID. In your case, training requirements are based on PositionTitleID. If you look at the 'simplified version' I posted on this thread, you will see that I created the table structure to mirror your requirements and eradicate that potential source of confusion for you.

And in this latest instance I see you've introduced a new issue: training type. If this is supposed to be about a course then the fk for trainingtypeID should be stored in the course table.

If you are having trouble taking a working model and adapting it to your purposes, even with help in explictly redesigning the tables, then I'm not sure I'm the person to help any further. The last example db I gave you provides the exact table structure you need. You may want to add/modify a few fields in some tables to provide other attribute data about those entities. Look at the relationships in that db (Menu>Tools>Relationships) to see how they should work together. But if you insist on changing the fundamental structure of the db model away from the example I posted then I'm sorry but I can't help any further.

 

[Hope]

So sorry, I'm so slow at this, its hard to think when people are in and out of the office and I'm trying to self train until my class in march. I will relook at everything, thanks for your time and help.

 

[Lightwave]

Hope - stick with it - and if you can go back to a known point where information/design was correct or at least adequate then taking things out and in and generally completely pulling things to pieces without the need to fix it can be very useful.

Most of us at some point get advice that while we know is correct is at a level that we don't understand and therefore struggle to implement.!

 

[Hope]

Thanks Lightwave, its nice to hear positive words. I will keep trying.

 

[Hope]

subforms

I have a form and I'm using a command button to open the form to enter information and I have the data entry property set to yes and the one text box is still showing the previous entry?

Then on some fields it says Field cannot be updated, noone else is in this db so they are not locking it up.

 

[CraigDolphin]

the one text box is still showing the previous entry

My guess is that the textbox is not bound to a field in the recordsource (ie, is unbound or calculated)

Then on some fields it says Field cannot be updated

Is your form bound to a query? Check to see if the query itself is updateable? Or, is the control on your form a calculated control? If so, then you won;t be able to change it.

If none of the above, try posting a ziped copy of the db and I'll have a look (AC2k format if possible)