DB Design question

[theKruser]

I am building a portion of my DB for training tracking. Variables in training to be tracked are two-fold: date contstraints and training type.

Date constraints:

One time certification
Fiscal year re-certification
Calendar year re-certification
Semi-annual fiscal year re-certification
Semi-annual calendar year re-certification
Quarterly certification

Training type:

Some require only a date to be tracked
Some date and score
Some date and classification
Some date, score, and classification

My question then becomes that of design. I am trying to get my mind around normalization and its constraints. As a novice designer, I am relying on the expertise of those who have been doing this far longer than me. As applied to the above, is it better to have several tables (i.e. one table for each training type/date constraint combination) or fewer tables with unused fields (i.e. table with date, score, and classification with no entry in columns for training types that do not require that criterion)? I am looking to track over 2,000 employees in almost 1,000 different training sets/sub-sets.

As always, thank you for your time and patience with this newbie!

 

[DCrake]

Simple Software Solutions

In almost all Access applications and database management systems there are fields in tables that are not used for all records, this cannot be helped. For example your employee table will hold fields such as contact details, Home phone, Mobile no, email address. Not all people will have all three but the provision is there should they have all three.

Bearing this in mind you would be better off just have one table with all the fields in it. In addition have another field that will hold a check digit.

Some require only a date to be tracked
Some date and score
Some date and classification
Some date, score, and classification

Give each combination a score such as

Date only = 2
Score = 4
Classification = 8

Each combination will produce a different number you can then use this to make sure the correct combination of data is recorded.

Also by using just one table it will be easier to obtain results and run queries and reports later on in your application.

CodeMaster:

 

[theKruser]

Give each combination a score such as

Date only = 2
Score = 4
Classification = 8

Each combination will produce a different number you can then use this to make sure the correct combination of data is recorded.

Thank you for the help; however, I do not understand. Can you please explain in more detail what the advantage of the scoring system you outlined is and how to employ? I am really new to this. I will combine all into one table, but am not sure what to do with the check digit. Thanks again for your help!!

 

[The_Doc_Man]

I'm going to get a little pedantic.

Normalization saves space and forces a certain type of organization. It makes things VERY easy to do once you isolate the data in the way that correctly reflects the relationships between the elements of your data set. So... what are the elements (and what are the properties of those elements)?

OK, "People" and "Certifications" are two stand-out elements. The relation is that a person has an arbitrary number of certifications from 0 to a gazillion.

Properties:

People have names, addresses, phone info, etc. There is a long list of possible things unique to a person that belong in the People table.

Certifications have person-holding-certificate, date-of-issue, period-of-validity, plus the options of score and classification.

The relationship would be best exercised by having a person-ID number in the People table as a primary key (which is commonly called PK) and use that same number in the certifications table to point to the person holding the certification (which in that usage is the FK). Then the number of certificates a person holds is the DCount of the certificate records having that person's ID number.

The fact of a person having a certification is that a record (with the person's ID) exists to be counted in the certifications table.

Now, with the certification codes that you list, you get some wrinkles. As a purist, you would not store the date of expiration of a certificate because you have a date of issue and a code for expiration. As a pragmatist, you might add one more field to the certificate record setting the date on which it expires based on the start-date and code. This is a situation that screams for a pre-defined list of codes and a simple-minded VBA statement in a module to compute the end-date for the certificate.

Now, at this point, having the certificate record is enough to show the certification. The details of score and classification are less material. You just have to remember that in some cases you need to specify default values for scores (such as 0 if no score is required) and classifications (such as "" or "0" if no classification is involved.)

Before we delve into the business of a VBA or other type of routine to handle that expiration date, it is more important for you to grasp the normalization aspect and logic of what I just described. So I'm going to leave this one hanging for a while until you are ready for that next step.

 

[theKruser]

I am understanding normalization more and more and I think I have a grasp, but am still not quite there. I understand the thinking around contact info and emergency contacts and the like since they are a one-to-one type data set. The problem I can't seem to figure out in the quest of understanding normalization is the gray areas: i.e. permanent certifications...they are dates to be stored, but they are also (in my mind) a one-to-one as well since the dates will never change per employee. I have my mind around the concept, but differentiating between this type of info is difficult for me to grasp.

 

[The_Doc_Man]

Permanent certifications are not one-to-one. If one employee has permanent certifications in two different subjects then it is many-to-one (with respect to certifications.) Don't let "permanence" confuse you. It is a property of a certification. Therefore, it has nothing to do with a person. The same certification could be held by multiple people. In fact, this make it a many-to-many depending on how you want to draw it or think of it.

Draw this on paper or on a dry-board somewhere.

Block at one side of the picture: Persons - and list some attributes of people. Make a list of potential persons whose certifications must be tracked.

Block at the other side of the picture: Certifications - include subject, type, classification, duration of validity (by code or otherwise). Make a list of proposed certificates you would honor.

Block in the middle: CertPers - make a list of records. For each certified person, draw one line to a person and one line to a certificate. You will see many lines to the same certificate and many lines to the same person. But you should only see two lines (one each way) from any one record in the middle block.

This middle block is called a junction table. What is IN that table becomes supremely important to your operation. A record in this junction table might include:

1. Person ID - the link to the person.
2. Certificate ID - the link to the type of certificate
3. DateIssued - obvious
4. DateExpires - the date after which this entry is no longer valid. THIS date is set according to information you supply about the certificate and the code in the certificate attributes field. Permanent certs? No problem, pick a date 50 to 100 years in the future.

Now, the only detail is to list each type of cert you can issue and then build your lists.

To search for certificates held by person X, search the junction table for all unexpired entries for person X and link back to the certifications through a JOIN query.

To search for PERSONS holding a specific type of certificate, search the junction table for all unexpired entries relating to certificate Y and link back to the persons through a JOIN query.

 

[theKruser]

I did exactly what you described and now understand the "why" behind the what. Thank you very much for your time in explaining. I will continue to research the types of quries and applications therein. Again, thank you for your time and help.