Database design question - Challenging!

[StephanieD]

I'm not sure how "challenging" this will be for you experts, but I am stumped.

I would like to write a new employee management tool for our company.

We have decided on the following five levels of classification to describe where a worker works: (its a large company!)

(1) Division {Manufacturing, Natural Resoruces}
(2) Region {NorthEast, SouthEast, Central, Mountain, West}
(3) Site {Chicago, Atlanta, Miami, Dallas, Pheonix, San Leandro}
(4) Department {Smelting Plant, Planermill, Mining, Jointing Plant}
(5) Crew {1, 2, 3}

So John Smith might be: Manufacturing.SouthEast.Atlanta.Planermill.2

That level of specificity may seem like overkill, but it is needed for more than just identifying where a worker is located (details available if necessary - it would overly complicate the question if I explain it here). So this system works just fine for all hourly workers.

But! When we try to put staff in there, it also works ok EXCEPT when you have someone, for example, who manages two Regions, say NorthEast and SouthEast.

With our old system, we have simply assigned them to one or the other and worked "around" the system. Everyone here (including programmers) complains about the old system, but when I ask how the tables should be constructed to fix these problems, no one can answer.

So that's my question - how would you design the data so that someone managing two Regions can fit nicely into a relational database?

 

[Banana]

I would probably do something like this:

Create a table to store all employee's names, personal information. No information about their job roles is kept in that table.

Create another table that stores information about a job, its roles and responsiblities.

Then one more table for locations.

Relationship would be then defined as:

Employee table --> 1 To Many --> Jobs table
Employee table --> 1 To Many --> Location table


The structure now allows you to assign multiple roles to one same employee (even if for 90% of your employee, it's just one roles, the flexibility is there, and if you really wanted to, you can now maintain a complete history of what past jobs they had) and multiple locations to the same employee, so for a region manager who has two region, the manager would have one entry in employee table, one entry in job identifying him as a region manager then two entry in location identifying his regions.

Did that help?

 

[StephanieD]

I guess that would be the correct way to do it wouldn't it. I was hoping there would be a way to keep everything in one table.

I forsee a lot of loops in my future - instead of looking up an employee's one and only job title, we now have to loop through tables looking for all the employee's jobs.

Thanks..

 

[Banana]

No need for loops; that's what queries are for, and you can use queries in code as well.

 

[tokoloshi]

Take a look at this app. I have not factored in the the various job roles because I figured that you can learn more by trying to do that yourself.

There are several ways that you can handle the relationships, but this was the quickest (I knocked out the entire app within +-30minutes)

Given your various scenarios, it may be possible that you will not exceed 100 regions, or 100 divisions, or 100 departments. So you could possibly create a single table and partition the table to let the numbers from 1-100 represent 1 aspect, 101-200 can represent a second aspect and 201-300 can represent another. This just complicates the issue and becomes difficult to maintain, negating any benefit gained by reducing the number of tables in the schema.

You had not clarified the role of the crews, so this represents my understanding of what your crews should be doing.

Sean

 

[tokoloshi]

Just as a matter of interest, the HR apps are the select few that effectively require tables to be designed that are related to itself recursively.

The classic HR personnel table will hold FName, LName, {Contact Details}, SupervisorID,RoleID,DepartmentID

The SupervisorID for the President would be NULL, so this has to be a NULLABLE field. The VP's SupervisorID would be the President's (1) and each other staff member downwards would contain the EmployeeID of the person above them in the organisation's organigram in the SupervisorID field.

Your requirement to have multiple roles performed within the organisation obviously points to a Jobs/Roles/Functions type of table that should link back to a functional organigram. This should therefore similarly contain a FunctionLevelID field that is used in a manner similar to the SuperVisorID field, allowing for NULL values and the President would once again contain NULL in this field. Subsequent roles would have the higher level roles linked in a similar manner to the SupervisorID as well.

I would also suggest an EncumbentPersonID that is a foreign key pointing back at the EmployeeID of the Employee table associated to each role.

I would like to see some of that thought played out in the sample DB that I uploaded and we could possibly all contribute to the thought process a bit more effectively then after you have uploaded any amendment back onto the forum?

 

[Banana]

Tokoloshi makes a good point about needing to looking up the same table.

If you need more examples, Allen Browne has a great article about "Self-Join" and how it can be used to look itself up recursively.

 

[StephanieD]

I am overwhelmed by all the help! Thanks very much, I will work through all this tonight.

About the loops I talked about... its sorta complicated...

Most of my programming has been done in Excel using vba because our company thinks that Excel files will be more maintainable in the event that someone gets hit by a bus (ie, leaves). I have told them that code is code but they insist, so I laughed, shook my head and wrote thousands of lines of vba code in Excel. However I refused to use Excel as a database, and keep the data over in Access, and pull it in using an external database query.

To complicate it further, we have an old app that only reads Access 95 databases, so that's what we're using, and we cannot create queries in it with Access 2003 that we use. So I am queriless. MS Query (that handles pulling the data over from Access) will allow you to filter a little bit, but it isn't really user friendly in terms of handing it a parameter and asking it to go fetch some rows where empl_id=123 or whatever. It handles empl_id=123 statically just fine, but programmatically I haven't been able to find a way to do it. So I just bring everything in, and then do while sheet1.cells(i,2)<>"".

I'll go take a closer look now at what you guys posted here. Thanks again.

 

[Banana]

Um, your company logic is ummm, interesting

Are they aware that both Access and Excel use exactly same language? That they'd have *less* code to maintain. More importantly, if you were to leave or get hit by bus (God forbid!) that thousand lines of code is likely to be a headache in walking through and they'd end up paying out of the wazoo for a contractor to clean it up for them?

As for Access 95, see if you can link to the table from your 2003 database and query against it. Pretty sure you should be able to query anything whatever is linked.

I'd really put my foot down and insist that they pull their heads out of wrong end of their anatomy.

Good luck!

 

[tokoloshi]

we have an old app that only reads Access 95 databases ... and we cannot create queries in it with Access 2003.

Have you tried attaching the A95 DB tables to A2k3? Once attached you can create any queries needed in A2k3 without requiring changes to be made to A95 apps.

This query process is also a lot more intuitive than MS Query.

Base on what you have said I think that you would be able to realize the value of the Access query ability very quickly.

Steps to link
I can't remember the steps exactly and I no longer have Access 2003 loaded, so forgive me if the steps are slightly wrong, but generally speaking these would be the steps...

1. R-Click on the empty space in the details window of you tables and choose import
2. Choose the Access data provider
3. Browse to & select your A95 mdb file
4. Choose the tables you want to link

Once completed you should have a different icon next to the tables than the one you are familiar with that signifies that the table is outside of your DB.

You can now develop Forms, Queries & Reports that rely on these attached tables as if they were inside of you application.

To create a query, go to the Query section and use a wizard to create you first query and just take a look at the results. You will find that 30minutes spent on these 2 exercises will relieve you of a lot of your current problems

 

[gemma-the-husky]

have you sorted this yet, and started programming, or are you still designing

you said in your first post

So that's my question - how would you design the data so that someone managing two Regions can fit nicely into a relational database?

the fact that you have already identified this issue indicates that the table design you envisaged does not meet the actual requirements for your system

one way round this is to nominate a main attribution/function for each employee. and manage exceptions/additional functions differently. Or you could decide that all functions for each employee are equally important, and design a structure that enables you to manage the data accordingly

Consider other possiblilties - Can an employee in more than crew? does he have to be in a crew?

the other thing is your five classifications

(1) Division {Manufacturing, Natural Resoruces}
(2) Region {NorthEast, SouthEast, Central, Mountain, West}
(3) Site {Chicago, Atlanta, Miami, Dallas, Pheonix, San Leandro}
(4) Department {Smelting Plant, Planermill, Mining, Jointing Plant}
(5) Crew {1, 2, 3}

this identifies some other subtle issues - you only have crews 1,2,3 - now if an employee is a member of the smelting plant dept/crew 1, and moves to the planermill dept, do you have a mechanism to update the crew also if necessary. If crews are dependent on the department, then strictly this is not a fully normalised structure. Even here there are other possiblilties - Can an employee be in more than crew? does he have to be in a crew?

as well as looking at your data structure to solve the issue of multiple attributes, you also need to decide what information you hold against each employee, so you need to decide whether any of these dependent on others, or are they all freestanding

ie - you may not need a region stored against the employee - i would think that chicago is NE region, Atlanta and Miami are SE region etc - so all you need to store is that the employee works in miami, and you can determine the region from that.

Further, are divisions also determined by the region or site.

then - do you want to manage the available plants etc based on the the site
ie if you dont mine in Chicago say, how do you prevent an employee working in Chicago being wrongly allocated to the mining dept. It may therefore be that you need another table to manage the active depts at each site - or it may be that this indicates a refinement is needed in the basic structure.

If this is just a rough and ready system to be used just be yourself it may not matter - but, if its going out company wide, then its worth spending a bit of time to get it robust and rigorous.

Finally for what its worth, I think Access VBA is far easier to use the Excel VBA. The form objects are easier to handle, there seems to be more properties exposed - and Access is far far quicker than excel for data manipulation.