DB Design Question

Question

I am designing an Org Chart, model is almost ready and simplified a bit for clarity here.

OrgUnit (OrgUnitId, Name, ReportsToOrgUnitId, ...)
OrgUnitJobs (OrgUnitJobId, OrgUnitId, JobName, ReportsToOrgUnitJobId, ... ,IsJobGroup)
Employee (EmployeeId, ........)
OrgUnitJobEmployee (OrgUnitJobId, EmployeeId, AssignedDate, .....,)

so I want to know every OrgUnit's ManagerEmployee (should have one), and Employees can have more than one job, but one of them has to be the main job, so I know whats his manager and other stuff. This is going to support a little workflow behind the scnese, so that is why it is not a very simple Org chart Model.

so what would you do, would you add properties like (IsManager property to OrgUnitJobs model) or add ManagerOrgUnitJobId to OrgUnitModel. and why?

Likewise, for employees would you add IsPrimaryJob property to OrgUnitJobEmployee model, or add PrimaryJobId to Employee Model.

Answer 1

I'm not exactly understanding the manager concept. Is a manager an employee or an organizational unit? Depending on your answer I'd have a ManagerEmployeeId or a ManagerOrgUnitJobId property on OrgUnit and/or OrgUnitJobs.

I would add PrimaryJobId to the Employee. That guarantees an employee only has one primary job.

So I guess in both cases I would favor an XxxId-type property over a boolean IsXxx property. An IsXxx property opens the door for multiple managers or multiple primary jobs.

Answer 2

You could consider adding 2 more tables (though i may be missing something):

OrgUnitManagers(OrgUnitJobID)  
OrgUnitJobEmployeeMain(OrgUnitJobEmployeeID) 

Can use referential integrity setting to ensure if an OrgUnitJob or OrgUnitJobEmployee record's are deleted these tables are updated automatically (if available on your DB)

-OR-

OrgUnitManager(OrgUnitJobID, OrgUnitId)  
OrgUnitJobEmployeeMain(EmployeeId, OrgUnitJobID)  

Can use referential integrity and now indexes to ensure uniqueness, though theres now duplication in the schema

Im a little confused as to whether the manager is specified at the OrgUnitJob level or at the OrgUnitJobEmployeeID level?? The above assumes its at the OrgUnitJob, but if its at the employee level, change OrgUnitManagers(OrgUnitJobID) to OrgUnitManagers(OrgUnitJobEmployeeID)