Table structure for company database

[meboz]

Hi all,

This is a fairly simple one, but im not sure exactly how to go about it...

Lets say a Company can have many Divisions. Each Division has 1 Division Manager (CGM), and manages many Cost Centres.

Each Cost Center has 1 Cost Centre Manager (CCM) and employs many Employees.

Im thinking of 3 tables:

Divisions, Cost Centres, and Employees. And if you hadnt already guessed, CGM's and CCM's are part of the Employees Table.

What is the appropriate table structure and relationships?

Thank you in advance...

 

[The_Doc_Man]

Name the tables as you like, but I'll be short and sweet in my examples.

tblDivsn
fldCDivID - autonumber or other unique identifier - primary key
fldCDivName - text, obvious
fldCGMEmplID - foreign key identifying employee who is CGM
other required data for division

tlbCstCent
fldCCID - autonumber or other unique identifier - primary key
fldCCName - text, obvious
fldCCMEmplID - foreign key identifying employee who is CCM
other required data for cost center

tblEmpl
fldEmplID - autonumber or other unique identifier - primary key
fldEmplFname, fld EmplMname, fld EmplLname - text, obvious
fldEmplCDivID - foreign key to division employing this person
fldEmplCCID - foreign key to cost center employing this person
other personal data

NOTE: Strictly speaking, you don't need both the CDivID and the CCID in the employee record if there is never a violation of the CCID-to-CDivID relationship. Then you could omit the CDivID 'cause it is implied in the CCID.

However, this structure does NOT allow an employee to cross-charge (be on loan to another cost center for special projects etc.)

IF that is part of your business model, then you need to remove the CDivID and CCID from the employee table entirely. Instead, you would have a fourth table as a link between CC and Employee.

tblCCEmpl
fldEmplID - foreign key to employee table - identifies employee
fldCCID - foreign key to cost center table - identifies cost center
fldHome - yes/no - YES if this is the employee's home cost center, NO if the employee merely has the right to cross-charge.

 

[meboz]

Thanks for the reply, i guessed that might have been the solution.

There is no cross-charging so i can omit the fldEmplCDivID from the employees table.

A question regarding the relationships between the tables...

Reltaionships b/w the employees table and the FK's in the CC and Div tables linking EmployeeID would be 1-1 correct?

Thanks again