Database normalization need help

[Johev]

Dear all,

I have been reading a lot of posts and watching some youtube videos mentioned in this forum about databases. Unfortunately I came to a point where I need actual assistance for my specific case and that is why I registered.

I am trying to build a database of pool secretaries that are assigned to different departments of my company. Right now they have 2 excel files with 8 tabs in total, which can't answer all the queries that the hierarchy would like to preform.

Description of the problem:

As an example we have 10 secretaries in a pool that are used in case of a need in any of the 28 departments (if a secretary at that department is not present). The minimum stay is 1 day, so if Dep1 has one secretary sick, they just request to be assigned one from the pool until their secretary returns.



The end output is the monthly assignment of secretaries to the different departments, by day.

The January Worksheet looks like this (of course the dates continue until the end of the month):

02/01/2012

03/01/2012

04/01/2012

05/01/2012

06/01/2012

07/01/2012

08/01/2012

09/01/2012

10/01/2012

11/01/2012

12/01/2012

13/01/2012.....continue

Number

ID

Contract

Name

Monday

Tuesday

Wednesday

Thursday

Friday

-

-

Monday

Tuesday

Wednesday

Thursday

Friday....continue

1

134546

Permanent

XYZ

Office Closed

Annual Leave

Annual Leave

Annual Leave

Annual Leave

Dep1

Dep1

Dep1

Dep2

Dep2

The explanation is that Secretary named "XYZ" with the ID "134546" took 4 days of "Annual Leave" in the beginning of the year and the following week she worked 3 days in Dep1 and 2 days in Dep2.


After studying the tables I have tried to take the advice from the videos and started normalization. But the time element in table has given me some troubles when trying to put it on paper.

There is a table for the secretary names, their contracts types above many other things and I came up with this, which I don't know how to link to this table.

I came up with this normalization (see attachment , but I don't know what to do now.

Thank you all in advance for all your help.

[jzwp11]

I looked at your design. First some recommendations, field and table names should not have any spaces or special characters. I always have an autonumber primary key field in each table. This is used to give each record a unique value and also to join to other tables.


Let me ask a couple of questions.

1. Will a secretary only have 1 nationality?

If so, then that part of your secretary table is stuctured correctly except that I would just reference the foreign key value; if the anser is no, then your table is not structured correctly and needs to look like this

tblSecretary
-pKSecretaryID primary key, autonumber
-PersonalNumber
-txtLastName
-txtFirstName
-fkGenderID foreign key to tblGender


tblNationality
-pkNationalityID primary key, auotnumumber
-txtNationality

tblSecretaryNationality
-pkSecNatID primary key, autonumber
-fkSecretaryID foreign key to tblSecretary
-fkNationalityID foreign key to tblNationality

2. Can a secretary have multiple languages?

If so, then your structure is OK, if not, then the same principle as above applies.

I'm not sure what PostID is, so you will have to explain in more detail.

Also, regarding the contract, can a secretary have multiple contracts of multiple types over time?

Is each contract for a secretary unique to that person?

Could you explain why you have a table for dept names and another for department number? Do the department names change over time?

[Johev]

Dear jzwp11,

Thank you very much for all your help. I will try to clarify the tables and please note that the names that are in the table with spaces are just for indication purposes, nevertheless I will take your advice and change them to avoid spaces. I am still new this and after I have something good enough on paper I will try to create it in Access 2010.

Now for the clarifications:
1) 1 secretary can have multiple nationalities. Right now we have some with 2 nationalities, but I would like the system to be able to be prepared for the future and that is why I include up to 4 nationalities for 1 secretary.

2) 1 secretary normally speaks at least 3 languages if not more, that is why I would like it to be able to select as many languages as needed. EU has 23 soon to have 24 official languages, which is with what I will start.

3) Let me try to explain the "post number" - from what I understood, the post numbers are fixes (imagine 10 numbers) and each secretary is assigned to 1 post. What this means for the permanent secretaries is that they keep 1 post numbers for ever, and only when they leave (retire or something else) it becomes vacant and is occupied by another permanent secretary.

In case of a temporary post, they stay in 1 post for 3 years and then they are substituted, nevertheless the post number is always the same.

Regarding the contracts, 1 permanent secretary can only have 1 contract. As for the temporary secretaries they can have 2 contracts, start with temporary and get a permanent contract in case they are offered one, and this means that 1 permanent post becomes vacant.

4) Department names and Department numbers: Normally each department name has 1 and only 1 number. This is due to the fact that Depname1 (can be called Smith, as the Head of that department is called Smith). To say Dep1 or Dep Smith in my company is the same thing, sometimes the number is even more important than the name, because not all people know all the last names of the Heads of the departments.

Nevertheless once, we had to change DepName9 with DepNumber9 to DepNumber10 and the DepName10 to DepNumber9. That is why I am trying to foresee this and don't make 1 table with the name and the number, but I keep 2 separate tables.

I hope that this will clarify a bit my issues and thank you and everybody who spends some time helping me for all your advice.

[jzwp11]

1) 1 secretary can have multiple nationalities. Right now we have some with 2 nationalities, but I would like the system to be able to be prepared for the future and that is why I include up to 4 nationalities for 1 secretary.

With the structure I proposed, you should be able to add any number of nationalities for each secretary.

If you adopt a similar design for the languages you can add any number of languages for each secretary.

3) Let me try to explain the "post number" - from what I understood, the post numbers are fixes (imagine 10 numbers) and each secretary is assigned to 1 post. What this means for the permanent secretaries is that they keep 1 post numbers for ever, and only when they leave (retire or something else) it becomes vacant and is occupied by another permanent secretary.

In case of a temporary post, they stay in 1 post for 3 years and then they are substituted, nevertheless the post number is always the same.

So basically, a post can have many secretaries over time (only 1 at any particular time). That still describes a one-to-many relationship and therefore, you should not have the post in the secretary table. Is there a need to track the secretaries associated with a post over time i.e. a history?

Regarding the contracts, 1 permanent secretary can only have 1 contract. As for the temporary secretaries they can have 2 contracts, start with temporary and get a permanent contract in case they are offered one, and this means that 1 permanent post becomes vacant.

Since a secretary can have multiple contracts (not considering the type of contract), then that describes a one-to-many relationship as well.

4) Department names and Department numbers: Normally each department name has 1 and only 1 number. This is due to the fact that Depname1 (can be called Smith, as the Head of that department is called Smith). To say Dep1 or Dep Smith in my company is the same thing, sometimes the number is even more important than the name, because not all people know all the last names of the Heads of the departments.

Nevertheless once, we had to change DepName9 with DepNumber9 to DepNumber10 and the DepName10 to DepNumber9. That is why I am trying to foresee this and don't make 1 table with the name and the number, but I keep 2 separate tables.

So are you saying that a department name is the name of the person who is in charge of the department and not the actual name of the department such as the Sales Department or the Engineering Department?

[Johev]

With the structure I proposed, you should be able to add any number of nationalities for each secretary.

If you adopt a similar design for the languages you can add any number of languages for each secretary.

Please have a look I have tried to apply the same design to the languages and other tables.

So basically, a post can have many secretaries over time (only 1 at any particular time). That still describes a one-to-many relationship and therefore, you should not have the post in the secretary table. Is there a need to track the secretaries associated with a post over time i.e. a history?

After further investigation I have concluded that a secretary either comes already with a post from a different department, or is assigned one of the free posts in the secretary pool. I think that I'll need a table just for the postID, and yes there might be someone asking to see how many and which secretaries have been on a specific post over the last years. As the person who is in charge explained to me, they recycle the posts and she has about 4 posts free right now, which have no secretaries associated to them.

Since a secretary can have multiple contracts (not considering the type of contract), then that describes a one-to-many relationship as well.

Should I create a table for contracts? I am not sure how to solve this one.

So are you saying that a department name is the name of the person who is in charge of the department and not the actual name of the department such as the Sales Department or the Engineering Department?

Yes, that is what I am saying. It is a very high political function. Imagine "DepNumber 1" also known as "Department Smith" is responsible for marketing while "DepNumber 2" also known as "Department Doe" is responsible for sales. The head of the departments stay there for 1 mandate (4 years). They might be reselected once, but they will probably be put in charge of a different department.

[jzwp11]

You will need to remove the fkLanguageID field in your secretary table.

After further investigation I have concluded that a secretary either comes already with a post from a different department, or is assigned one of the free posts in the secretary pool. I think that I'll need a table just for the postID, and yes there might be someone asking to see how many and which secretaries have been on a specific post over the last years. As the person who is in charge explained to me, they recycle the posts and she has about 4 posts free right now, which have no secretaries associated to them.

Then you will need a table for the posts and then one that joins posts & secretaries much like you did for the languages.

tblSecretaryPosts
-pkSecPostID primary key, autonumber
-fkSecretaryID foreign key to secretary table
-fkPostID foreign key to post table

Now, with each secretary/post combination you can have multiple events: i.e. assigned to the post or leaves the post. Now if these were the only 2 events, some people would ignore normalization and just add 2 corresponding date fields to tblSecretaryPosts, but what if secretary 1 gets assigned to the post A on 1/1/10, then goes on a leave of absence on 12/1/10, so the post has to get reassigned to secretary 2 for a period of time. Now secretary 1 comes back from leave on 3/1/11 and the department head wants secretary 1 back at the same post as they were before the leave. Now you will have 3 events for secretary 1 at post A

1/10/10: assigned
12/1/10: unassigned due to leave
3/1/11: assigned

So we really need a table that captures the event timing for a secretary/post combination

tblSecretaryPostEvents
-pkSecPostEventID primary key, autonumber
-fkSecPostID foreign key to tblSecretaryPosts
-fkEventID foreign key to tblEvents
-dteEvent

tblEvents (holds various events such as assigned, unassigned/assignment end, etc.)
-pkEventID primary key, autonumber
-txtEventName

You would use a query to detemine which posts need a secretary. That query would first look at tblSecretaryPostEvents and pull the most recent event date and event type. If the most recent event was assignment end, you know that post needs a secretary. You will also have to determine what post has never had anyone assigned; that can be done with a query as well.

Should I create a table for contracts? I am not sure how to solve this one.

What type of information do you want to capture relative to a contract?

Yes, that is what I am saying. It is a very high political function. Imagine "DepNumber 1" also known as "Department Smith" is responsible for marketing while "DepNumber 2" also known as "Department Doe" is responsible for sales. The head of the departments stay there for 1 mandate (4 years). They might be reselected once, but they will probably be put in charge of a different department.

That is kind of strange, but still a department has a function. And a department can have many heads over time and the name of the department is tied to the name of the head of the department. So first a table to hold the departments by function

tblDepartments
-pkDeptID primary key, autonumber
-txtFunction

tblDepartmentHeads
-pkDepartmentHeadID primary key, autonumber
-fkDeptID foreign key to tblDepartments
-fkPeopleID foreign key to tblPeople
-dteEffective


tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

Now I show dteEffective (effective date) of when the person (fkPeopleID) became head of the department (fkDeptID). If when the head person leaves the department, another person is quickly assigned, it would just be a new record in tblDepartmentHeads with a new effective date. Now if there may be a time gap between when one head leaves and another is assigned, you can approach it the same as we did with the events related to secretaries/posts.

Now since secretaries are people too and the basic fields in the two tables are probably similar, normalization says that like data should be in 1 table, so the department head people and the secretaries should all be in tblPeople. Therefore, wherever we showed fkSecretaryID in earlier tables, those need to be replace with fkPeopleID.

Now to create the department name with the last name of the department head, you would actually not store the name as such but rather use and expression to create it "Department " & tblPeople.txtLName

You can include a field in tblPeople that distinguishes a person by their role or job title.

This leads to another question, what is the relationship between departments and posts?

[Johev]

Dear jzwp11,

First of all I want to thank you very much for all the help you have been giving me. It is incredible how fast and correctly you can process the information that I give you and post back a constructive reply. I am sorry for not replying as fast as I would like to, but it takes me some time to fully understand what you have written, and to try to build on the information / suggestions that you have given me. Another thing is that as this is a project I do off peak hours at work, thus one day I might have enough to have a good look at it and another day I might not be able to see it at all.

Now, let's talk business, hehe

PostID

Your interpretation of the PostID is more complex than we actually have.
1) The PostID stays with the secretary for the entire time she is in the secretary pool. It can change if the secretary becomes part of the permanent staff, but in the normal case after 3 years with us the secretary leaves and the PostID that was assigned to her becomes vacant again.

It is not affected by her going on sick leave or holiday. She will keep the PostID as long as her salary is paid by us.

Nevertheless the event table could hold all the information about the secretary availability.
tblEvents
-pkEventID primary key, autonumber
-txtEventName (inside we would have):
- Available
- Annual Leave
- Maternity Leave
- Sick Leave
- Training
- ESO
- Competition (in case she has an exam to become permanent staff)
- Other

Would something like this work? If the secretary would be available, could her name be assigned to work for any number of days and a minimum of 1 day to any department?

You would use a query to detemine which posts need a secretary. That query would first look at tblSecretaryPostEvents and pull the most recent event date and event type. If the most recent event was assignment end, you know that post needs a secretary. You will also have to determine what post has never had anyone assigned; that can be done with a query as well.

The secretary assignment is done by a human on a basis of competency, preference of the individual departments and promises made to that department. Thus the only thing that I want to do is be able to check how many secretaries a given PostID had over the last 5 years. I will not feed the database all the information form the previous years, but in the future that could be asked.

tblPostNumber

I would like to ask if this table would work as it is now? I have created it with the pkPostNumberID not being automatically assigned by Access, but to allow me to introduce all the 6 number combination manually for all the 10 posts that we have.

I would than assign a PostNumber to each secretary, so that the all had one. Would this work?

I have more questions but I don’t want to complicate this even more, so I will just add the screenshot of how the database looks right now and I will try to clarify any doubts that you might have about it.


Thank you once again for all your help.

[jzwp11]

Your interpretation of the PostID is more complex than we actually have.
1) The PostID stays with the secretary for the entire time she is in the secretary pool. It can change if the secretary becomes part of the permanent staff, but in the normal case after 3 years with us the secretary leaves and the PostID that was assigned to her becomes vacant again.


...how many secretaries a given PostID had over the last 5 years.

Since the postID can have many secretaries associated with it over time, the structure I posted is the best way to handle it and you can gather the statistics you mention as well.

Nevertheless the event table could hold all the information about the secretary availability.
tblEvents
-pkEventID primary key, autonumber
-txtEventName (inside we would have):
- Available
- Annual Leave
- Maternity Leave
- Sick Leave
- Training
- ESO
- Competition (in case she has an exam to become permanent staff)
- Other

If you mean that the following are RECORDS in tblEvents then I would agree

- Available
- Annual Leave
- Maternity Leave
- Sick Leave
- Training
- ESO
- Competition (in case she has an exam to become permanent staff)
- Other

Would something like this work? If the secretary would be available, could her name be assigned to work for any number of days and a minimum of 1 day to any department?

That should work as well.

I would like to ask if this table would work as it is now? I have created it with the pkPostNumberID not being automatically assigned by Access, but to allow me to introduce all the 6 number combination manually for all the 10 posts that we have.

You can use an non-autonumber field as a primary key or you can add an additional field to the post table to capture your PostID, but still use the autonumber field for joining purposes. That choice is yours. In your forms, you typically don't show the key fields anyways only the ones that are significant to your users.


Regarding your attachment, I would suggest that you enforce referential integrity on your relationships. Checking the cascading deletes is generally not a good idea. Checking cascading updates should be OK.

In terms of relationships, the only real issue I see is with the two tables permanentstaff and temporary staff. Whether temporary or permanant, both are just types of contracts. Now you said that a secretary can move from temp to perm and thus their contract type would change, so how about this

tblContractTypes (2 records in the table: perm & temp)
-pkContractTypeID primary key, autonumber
-txtContractType

tblSecretaryContracts
-pkSecContractID primary key, autonumber
-fkSecretaryID foreign key to tblSecretary
-fkContractTypeID foreign key to tblContractTypes
-dteStart
-dteEndProb
-dteEnd



Technically speaking having 3 date fields suggests a non-normalized structure, so I will leave it to you whether you want to break the events (start, probably end, end) into records in a related table.

[Johev]

Dear jzwp11,

I have made some changes in the database structure which I will annex for you to have a better look at. I had also realised that it is better to have the PK as an auto-number, mostly when copy pasting things from excel tables, that is why I have remade some tables so that the PK is always an auto-number.

As for the tblSecretaryPostEvents
-pkSecPostEventID primary key, autonumber
-fkSecPostID foreign key to tblSecretaryPosts
-fkEventID foreign key to tblEvents
-dteEvent

I have divided the dteEvent into dteStartEvent and dteEndEvent, so that it could be inserted when the person went on leave and when he/she came back. Do you think it is OK, or should I change it? Would I have a problem with a person who would be Available (I would have the dteStartEvent but I would not know until when she would be unavailable, as she could get sick or something else any-time)?

I have also adopted the same structure that you have advised me for the:
tblSecretaryContracts
-pkSecContractID primary key, autonumber
-fkSecretaryID foreign key to tblSecretary
-fkContractTypeID foreign key to tblContractTypes
-dteStart
-dteEndProbation
-dteEnd

The dteEndProbation is always 9 months after the dteStart, this is when the probation period ends for both parties. Could I have this in a way that the database would automaticly calculate the dteEndProbation as dteStart+9months?

Now I need your help with inserting the information in the database. I never thought this would be a hard thing but I was so far unable to find out how to do it. For example, if I want to add a new secretary I go to the tblSecretary, I write her/his last and first name in the txt fields, I insert the SecretaryBadgeID and now I don't know what to do. I would like to add languages to the secretary and assign a gender (m or f), a nationality and so on, but I have no clue how to do it. I have the tbllanguages with all the available languages that they could speak and the same for the nationalities.

The tables are linked correctly but I just don't know how to work from this point.

Thank you once again for all your help with this database.

[jzwp11]

I have divided the dteEvent into dteStartEvent and dteEndEvent, so that it could be inserted when the person went on leave and when he/she came back. Do you think it is OK, or should I change it? Would I have a problem with a person who would be Available (I would have the dteStartEvent but I would not know until when she would be unavailable, as she could get sick or something else any-time)?

I think you should be OK with that design change.

The dteEndProbation is always 9 months after the dteStart, this is when the probation period ends for both parties. Could I have this in a way that the database would automaticly calculate the dteEndProbation as dteStart+9months?

Since it is always 9 months the value can be calculated. As such, you do not and SHOULD NOT store it in the table (calculated values should not be stored except under very special circumstances), just calculate it when you need to display the probation end date. It would be best to use the built-in dateadd() function: dateadd("m",9, dteStart)

Now I need your help with inserting the information in the database. I never thought this would be a hard thing but I was so far unable to find out how to do it. For example, if I want to add a new secretary I go to the tblSecretary, I write her/his last and first name in the txt fields, I insert the SecretaryBadgeID and now I don't know what to do. I would like to add languages to the secretary and assign a gender (m or f), a nationality and so on, but I have no clue how to do it. I have the tbllanguages with all the available languages that they could speak and the same for the nationalities.

The tables are linked correctly but I just don't know how to work from this point.

The next step is to create forms. All user interaction with the database should be through forms. The users should NEVER have access to your tables. In general, I typically base a (main) form on the table that makes up the one side of a one-to-many relationship and then use a another form (subform) based on the table that makes up the many side of the relationship.

For example, I would create a form bound to the secretary table. Access may create a subform automatically for you, I would just delete it. Save that form. Next create a form based on tblSecretaryLanguange. I would typically build this form as a datasheet (In Access 2007/2010: Create: forms-->More Forms--->datasheet). You can have the form wizard build it for you and the edit it. In terms of editing, I would remove the text box control that the wizard creates for the fkLanguageID field and replace it with a combo box based on tblLanguages. I would use the combo box wizard for this. Save this form. Open the secretary form you created in design view. In the navigation paine, highlight the form you created based on tblSecretaryLanguage and drag and drop it into the detail section of the secretary form. Access should automatically link the form you dropped (now called a subform) to the secretary form (now the main form) based on the relationships you have created in the relationship window. You would use the same approach for the nationality information for the secretary.

The video tutorials on this site might be helpful