Q on designing and relating these 2 tables

[CEV]

Hi, I am attempting to create a turnover databse for our organization. The
main things they want to see info on is the "Postition Number". Like how
many times that position has turned over in a period of time, and details
about the position such as FT or PT and the hours related to that position
(we have hours that vary around the clock). They would also like details
about the employees that have been in that position; like there age, if they
are a college student, there wage, what training they have completed, and so
on. We are a non-profit organization that provides services to people with
disabilities.

These are the tables I have come up with so far:

POSITIONS - Fields:
Position Number - *Primary Key &
Relationship Field
Department
Title
FT or PT
Position Hours - 40hrs, 20hrs, etc.
Scheduled Hours - 8am-3pm, 9am-5pm, etc.
Active or Inactive
Open or Filled
Benefits
Notes

EMPLOYEES - Fields:
Name
Employee Number - *Primary Key
Position Number - Relationship Field
Date of Hire
Date Left
DOB
Gender
Student or Non-Student
Training Completed
Wage

Please let me know if I am on the right track. Each "Position Number" can
have multiple entries as we want to keep a record of employees that have
been in that position.

Thank You,

CEV

 

[tina]

your table POSITIONS describes each position, as one position per record in
the table. good. i'm not sure i'd include ScheduledHours in this table,
however. also, you say

Each "Position Number" can
have multiple entries as we want to keep a record of employees that have
been in that position.

that is not possible in this table, because you have designated
PositionNumber as the primary key. read on, and you'll see that it's not
necessary, anyway.

table EMPLOYEES describes each employee, again as one employee per record in
the table. good. but this table should NOT be linked to the POSITIONS table,
so remove the PositionNumber field.

one position may be filled by many employees (over time), and i'm guessing
that one employee may be assigned to more than one position (again, over
time). so POSITIONS and EMPLOYEES has a many-to-many relationship. in
Access, you must express such a relationship with a "linking" or "junction"
table that acts as the "many" side of a one-to-many relationship with each
of the other two tables. so create a third table, as

tblEmployeePositions
EmplPosID (primary key)
EmployeeNumber (foreign key from table EMPLOYEES)
PositionNumber (foreign key from table POSITIONS)
StartDate (date employee was assigned to this position)
EndDate (date the assignment ended)
< you might want to put ScheduledHours in this table, instead of in table
POSITIONS. >

the relationships are
POSITIONS.PositionNumber 1:n tblEmployeePositions.PositionNumber
EMPLOYEES.EmployeeNumber 1:n tblEmployeePositions.EmployeeNumber

recommend you read up on data modeling and normalization; once you
understand those principles, you'll be in a better position to analyze your
organization's process and structure the database to properly support it.
see http://home.att.net/~california.db/tips.html#aTip1 for more information.

hth

 

[CEV]

Thank you very much for your response Tina. I will take a look at the site,
looks like some good information. I will try what you suggested with the 3rd
table. I have one question though with the EmplPosID (Primary Key) field. We
do not currently have such a number. Could this be an autonumber field?

Thanks,

CEV

 

[Steven Cheng[MSFT]]

Hi CEV,

For your issue, as Tina mentioned, it would be more flexible to separate
the relation between position and employee to another table, this table
contains the posID, empID and other information.
Based on my experience for designing, I suggest you create the third table
like this,
table name: pos_emp
fields:
EmployeeID (prime key, foreign key from table EMPLOYEES)
PositionID (prime key, foreign key from table POSITIONS)
(Any information you want to put, like startDate, daysRemaining and so on)

generally speaking, in relationship model, the many-to-many relation can be
present as following instruction:
1, create a relation table contains the two prime key of the original
tables, and set these fields to be prime key(yes, two prime keys in one
table is ok).
2, define foreign keys to insure the values of the fields are valid from
the original tables.
3, if you have other information about this many-to-many relation , just
add those fields into the new created table

I appreciate your understanding and hope the above information helps, if
you have any issues or concerns please let me know. I will be happy to be
of further assistance.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[tina]

CEV, sorry to be so long, just got back to this thread. to answer your
question:

I have one question though with the EmplPosID (Primary Key) field. We
do not currently have such a number. Could this be an autonumber field?

yes, it can be Autonumber; that is the data type i would use in this
scenario. some developers prefer to use the two foreign key fields as the
primary key in a linking table (sometimes another field, such as a date
field, is required to ensure unique-ness). i personally don't like to work
with multi-field primary keys, so i rarely use them, preferring to add an
Autonumber field and designate it as primary key.

the above will make more sense to you when you've done some study on data
modeling and normalization principles.

hth

 

[CEV]

Thanks Tina, appreciate the response.

CEV

CEV, sorry to be so long, just got back to this thread. to answer your
question:


yes, it can be Autonumber; that is the data type i would use in this
scenario. some developers prefer to use the two foreign key fields as the
primary key in a linking table (sometimes another field, such as a date
field, is required to ensure unique-ness). i personally don't like to work
with multi-field primary keys, so i rarely use them, preferring to add an
Autonumber field and designate it as primary key.

the above will make more sense to you when you've done some study on data
modeling and normalization principles.

hth