Can these tables be normalized further? 1

[JamesMack]

I am in the process of designing a database to monitor the training and professional qualifications of the personnel within my organization. Having normalised I am left with the following tables:

tblPersDetails
PersonalNumber(primary key)
LastName
Firstname
Address1
Address2
etc
(This table contains details for up to 120 persons).

tblPersTrainingObjective
PersonalNumber (composite key)
TrainingObjectiveNumber (composite key)
DateAchieved
Performance

tblTrainingObjective
TrainingObjectiveNumber (primary key)
Description
(This table contains 168 training objectives)

tblPersQualification
PersonalNumber (composite key)
QualificationNumber (composite key)
DateAchieved

tblQualification
QualificationNumber (primary key)
Description
(This table contains 32 possible professional qualifications)

tblPersAnnualQualification
ServiceNumber (composite key
AnnualQualificationNumber (composite key)
DateAchieved
Score
Pass/fail

tblAnnualQualification
AnnualQualificationNumber (primary key)
Description
PassCriteria
(This table contains details on 12 qualifications which some or all personnel have to re-qualify annually)

With the tables as they are shown tblPersTrainingObjective will contain data on 168 (training objectives) x 120 (persons)=20160, as it will obviously list each persons objectives seperately. tblPersQualification will be 32 x 120=3600.
Is it possible to alter the design to reduce table size or can Access 97 manage with the tables in this format?

 

[ErikZ]

If the majority of those tests in tblPersAnnualQualification are going to be blank, you might want to design your table to only have actual test scores in it. By designing your database to insert test results as they happen.

Just an idea.

Later,
ErikZ

 

[tonedef]

To be normalized the addresses would have to be pulled out of the person table and have their own table.

 

[JamesMack]

Thanks ErikZ and tonedef for your responses.
ErikZ, can you expand on your idea?
Could you also tell me whether Access will actually cope with the tables as they are? The tblPersTrainingObjectives will be extremely large, at worst 20160 records.

 

[JerryDennison]

In defference to tonedef, you do not need to pull the address info out of the personal info if you are merely going to maintain their current address and not track their movement history. As for 20k records in a single table, this is not excessively large and easily handled by Access. The rest of your structure is pretty good with an exception or two.

1) I would not have separate tables for Qualifications. One table with an Annual checkbox would be sufficient and make reporting considerably easier.

2) You might even consider keeping Qualifications and Objectives in the same table as they appear to be one and the same. You could have a field to denote the type of training a particular item is.

The only reason I say this is because the tables do not contain a large number of fields so performance would not be reduced. Nor would grouping this Training information violate Normalization Rulse(based on what I see here anyway). Just something to think about.

 

[MichaelRed]

In general, I would agree w/ Jerry. Excessive "Normalization" actually hurts performance in many situations. With an estimate of 20K records, you will not see a performance penalty for less than optimal normalization, but you will need additional code/queries to properly relate and maintain the data.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[tonedef]

Jerry,

In a real world situation no you don't need to pull anything out. You could put everything in one giant table. However, the question was can these tables be normalized further? Yes, they can. This doesn't even meet 1st normal form because of the repetitive data of Address1 and Address2. By definition this data should be its own table. Even if they don't want to track history what happens when one person has 3 addresses. Then this table would have to get a new field Address3, and there would be a lot of wasted space because all but one of the Address3 fields would be null.

tone

 

[THoey]

Jerry, I think Address1 and Address2 are the different lines for the same address, as in:

Joe Smith
123 Main St.
Apt #123
Smalltown, USA 99999

I think they can stay... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ErikZ]

I'm sorry, I don't see where "Address1" and "Address2" are repeated guys.

"Thanks ErikZ and tonedef for your responses.
ErikZ, can you expand on your idea?"

Hmm, when I read your post it looked like you had already set up a full table, without the scores. Now I see that you were saying that you will SOMEDAY be using that much space.

It doesn't look like it will be a problem though, according to the "Access Elders" here, access can handle tables that size.

 

[tonedef]

Erikz,
I assumed Address1 and Address 2 were 2 seperate addresses. If that is a correct assumption then 'address' information is repeated. Meaning a single record holds the information that should be held in 2 distinct records. However, someone later in the discussion pointed out that they thought Address1 and Address2 where really the first and second line of the same address. Then, no the address information is not repeated. I still think address info should be in its own table, technically, but its a matter of style and preference.

 

[THoey]

Woops, sorry Jerry, I addressed my post to you instead of tonedef. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[JamesMack]

Many thanks to all of you, for your help.
I can see now that the wording of the question was not ideal!
Address1 and Address2 are just separate lines of a single address, sorry.
The question perhaps should have read 'Is this structure ideal for the purpose I intend to use it?
Can anyone suggest a better way bearing in mind the possible size of the tblPersTrainingObjectives?
Has anyone any general advise regarding possible problems?
It would be possible to upgrade to Access 2000; would there be any specific advantage for this project?
(This is the first time I've used Access).

 

[ErikZ]

I think you're freaking out too easily on the size. Assuming your database contains 20160 cells, and that a cell averages 50 bytes. Your entire database comes out to less than a meg of space!

(20160*50)/1024=984.375k

I think you ought to go and investigate how big of a database access can handle and still operate without error.

Later,
ErikZ

 

[JerryDennison]

tonedef,
Address1 is the first line of an address:
i.e. 418 West Lake Avenue
Adress2 would the the second line of the same address:
i.e. Suite 15A

They do not violate the 1st, 2nd, or even 3rd Normal Form as they are two distinct descriptors of the same entity.

 

[Quehay]

Hey why not jump in and throw some punches? Multiple address lines are distinct attributes, not repeating data (are first and last names repeating data?). To be completely normalized an SSN could be broken into meaningful subcomponents, as could a zip code. A last name is common to more than the entity determined by the primary key, so create a last names table and you can be in true 2nd normalization. Jerry and Michael are dead on regarding normalization--it's great in theory books but the needs of any particular context prevail. In Access you'll bring the entire contents of both tables over for a join, so normalization can have a negative impact on performance.

Also: Access doesn't handle concatenated primary keys well--in Access you're better off creating an autonumber ID field for joins.