Database Design for Tracking Employee Positions

[Jgonz1659]

I have just been tasked with creating a positions trackingdatabase and I am trying to figure out the best way to structure it. My goal is to be able to track positions,employees in those positions, and compare budget to actual salaries. I have attached a PDF document with my tables. I’mhoping that someone in this forum has created a similar database and canprovide some guidance. Here are my questions:


1. Do I need a separate table for actual andbudgeted positions or should I just create one table with both actual andbudgeted information? We currently have employees in unbudgeted positions, soif I create two tables, then it might be difficult to compare. For example ,whenI create the relationship between the tables, my query will only return one ofthe following:
a. All records from the budgeted positions table
b. All records from the actual positions table
c. Only include rows where the joined fields fromboth tables are equal.
d. Note: These options will not allow me toaccurately view and compare budget to actual information.
2. I would like to produce a vacant positionsreport. Will I need a vacant positions table or should I just include a fieldwith a YES or NO to identify if the position is vacant?

[Xipooo]

I have just been tasked with creating a positions trackingdatabase and I am trying to figure out the best way to structure it. My goal is to be able to track positions,employees in those positions, and compare budget to actual salaries. I have attached a PDF document with my tables. I’mhoping that someone in this forum has created a similar database and canprovide some guidance. Here are my questions:
1. Do I need a separate table for actual andbudgeted positions or should I just create one table with both actual andbudgeted information? We currently have employees in unbudgeted positions, soif I create two tables, then it might be difficult to compare.

For example ,whenI create the relationship between the tables, my query will only return one ofthe following:
a. All records from the budgeted positions table
b. All records from the actual positions table
c. Only include rows where the joined fields fromboth tables are equal.
d. Note: These options will not allow me toaccurately view and compare budget to actual information.
2. I would like to produce a vacant positionsreport. Will I need a vacant positions table or should I just include a fieldwith a YES or NO to identify if the position is vacant?

Yes, separate your data. Do it as MUCH as possible while still remaining reasonable. In general I split my data up into different groups of nouns and adjectives. If it IS something or is the description of something, it goes in it's own table. The fewer rows and fields your queries have to search through, the better. JOINS are faster than flat tables of null data.

I don't see why you would be returning "ALL" the records from any table. When you run a query, the whole point is to be as specific as possible to the question the user is asking. Things like date ranges, aggregates, and comparison of values to specific elements within your data need to be used extensively. Otherwise the results will be confusing and usually irrelevant to what the question is the user is asking.

The other thing to remember is that not everything has to be a direct relationship with other tables. Many-to-many relationships are my favorite tool to solve most relational data mapping. That is to say, a 3rd table which explains the relationship between two other tables is usually the smartest way to explain how data fits together.

[Jgonz1659]

Thank you very much for your response!

"I don't see why you would be returning "ALL" the records from any table."

In order to view and compare budget to actual data correctly, My query needs to include:

1. all rows in the budget table and have zero where there is no match in the actual table
2. all rows in the actual table and have zero where there is no match in the budget table (we will have unbudgeted positions or employees that get paid more than what was budgeted)

I would like to create a report that shows the following fields:

Department
Manager
Position
Employee
Budget
Actual
Variance

I hope this is clear. Thanks!

[Xipooo]

Thank you very much for your response!

"I don't see why you would be returning "ALL" the records from any table."

In order to view and compare budget to actual data correctly, My query needs to include:

1. all rows in the budget table and have zero where there is no match in the actual table
2. all rows in the actual table and have zero where there is no match in the budget table (we will have unbudgeted positions or employees that get paid more than what was budgeted)

I would like to create a report that shows the following fields:

Department
Manager
Position
Employee
Budget
Actual
Variance

I hope this is clear. Thanks!

Sounds like you have your work cut out for you then.

Have you made your tables yet and split them up into more organized pieces?