database structure

[KK]

Hello,

I'm setting up a staff database and need some advice on how to structure
it.

As well as listing names, addresses, telephone numbers, etc., I want to use
the database to define the management structure. The company is split into
groups (each with a group leader), each group has tasks (each with a task
leader) , and each task has 3 or 4 staff.. Can I put all our personnel into
one table, or should I start with separate tables for group leaders, task
leaders & staff ?

How do I define the management structure between the various personnel?

Thanks

KK

 

[Jeff Boyce]

One approach is to add a "SupervisedBy" field to hold the PersonID of the
person who manages a specific individual. The only person row without a
value in SupervisedBy would be the CEO. Everyone else would have someone
"over" them.

 

[Douglas J. Steele]

There's also a possibility of "matrixed" organizations. For example, I
officially have 2 managers: a functional one and an administrative one. In
that case, you'd need to have a second table that stores the details of who
supervises whom.

In the case above, there would be two rows in that second table. One would
have the Id of my functional manager as SupervisedBy, and the other would
have the Id of my administrative manager as SupervisedBy. Both would have my
Id as the "Supervised" field. You'd probably also want a third table that
indicates "Type of Supervision", and include the Id from that table in the
second table as well.

 

[PC Datasheet]

TblStaff
StaffID
FirstName
LastName
StreetAddress
City
Province
PostalCode
BusinessPhone
HomePhone
CellPhone
etc

TblGroup
GroupID
GroupName
etc

TblStaffInGroup
StaffInGroupID
GroupID
StaffID
GroupLeader (Yes/No)

TblTask
TaskID
TaskDescription
etc

TblGroupTask
GroupTaskID
GroupID
TaskID

TblStaffForGroupTask
StaffForGroupTaskID
GroupTaskID
StaffID
TaskLeader (Yes/No)