0

I have an old database design that I think I can streamline and make more normalized. I was hoping to get some thoughts on this. Here are the “rules” for the database:

  1. The organization is made up three hierarchies:

    a. Bureau - is the highest level
    b. Office - each bureau can have many offices
    c. Division - each office can have many divisions

  2. We have employees that work at all of the three tiers. For example:

    a. We have Bureau level staff. They do not belong to an office or division. The oversee all offices, but do not belong to them
    b. We have Office level employees, that do not belong to a specific division, but oversees all divisions
    c. Division level folks that belong to a division

  3. We also have projects. A project can be at various levels:

    a. Bureau wide projects
    b. Projects that span multiple offices
    c. Projects just in a division

  4. Every project needs to be managed by at least one employee, having a specific role (project manager) tough the employees table would be made of individuals with other roles, and even multiple roles.

Currently, I have the following schema (it’s abridged for brevity):

enter image description here

With this schema, I have found the following issues:

  1. All projects are tied to a division, in this model. But, in reality, not all projects are directly attributable to a division. Some maybe at the office or bureau level. Others may be across multiple offices or bureaus (but not across multiple bureaus AND offices). So, I get around this by having extra options in each of the organization Tiers. For example. In the Division Table, I have an option for Office Wide or Bureau Wide. Or I might have an options like Office One/Office Two.

  2. Another issue I see: each of the roles tables have many duplicate values. For example, if we look at divisionRole table. Almost all divisions have the same role. So, I have DivisionManager, divisonPM, divisionLead listed multiple times (once for each division). Sometimes, a division may have a unique position, but this is rare. This is true for the BureauRole and OfficeRole tables

So, I am looking for advice on how to better normalize this database, and address the above scenarios. Anyone have suggestions?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jason
  • 3,821
  • 10
  • 63
  • 120
  • `it’s abridged for brevity` ... how big is the actual schema then? – Tim Biegeleisen Mar 31 '17 at 15:43
  • about 25 tables. Most tables are one-to-many about the ProjectsTable. For example, there's a table called ProjectType. Each project can be of a particular type. – jason Mar 31 '17 at 15:45

1 Answers1

1

You could simplify this database design significantly by replacing your three distinct organizational levels with a single organization table that has a self-directed foreign key which indicates the parent.

Consider something like this:

CREATE TABLE ORGANIZATION
( org_id         int IDENTITY NOT NULL
, org_level      CHAR(1) NOT NULL
, [name]         VARCHAR(50) NOT NULL
, parent_org_id  INT
, constraint pk_org PRIMARY KEY (org_id) 
, constraint fk_org_hierarchy FOREIGN KEY (parent_org_id) 
  REFERENCES ORGANIZATION (org_id)
, constraint ck_org_level CHECK 
  (   org_level = 'B' -- Bureau
   OR org_level = 'O' -- Office
   OR org_level = 'D')-- Division
, constraint ck_org_root CHECK
  (  (org_level = 'B' AND parent_org_id IS NULL)
  OR (org_level <> 'B' AND parent_org_id IS NOT NULL) )
);

Now you have all of your organization levels in one table, which means all of your many to many intersection tables can be collapsed from three to one as well, and you have the ability to assign other foreign key relationships, such as the one from project to any level of the organization.

Note that you can add other constraints too, so that you can enforce your business rules such as:

4.Every project needs to be managed by at least one employee, having a specific role (project manager) tough the employees table would be made of individuals with other roles, and even multiple roles.

One thing to keep in mind is that managing hierarchical data in a relational database is a little tricky. However, there are things that you can do to make this simpler. For example, see my answer to this question for more about managing hierarchies in an RDBMS.

Community
  • 1
  • 1
Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • I like the idea of using a recursive table, for the organization. But, how would this address my second concern: With respect to roles, I often times have duplications. For example, every division has a PM. So, If I have 5 divisions, I now have 5 "PM" roles in the Roles table. Any thoughts on how to best normalize that? – jason Mar 31 '17 at 18:26
  • @jason - The real problem with your roles that I see from a normalization standpoint is that the names are set once per division. This creates an update anomaly if you have say, 5 instances of "Project Manager". What if your organization decides to change this role name to "Six Sigma Black Belt"? If this is not your concern, then just leave it as is. (continued...) – Joel Brown Mar 31 '17 at 19:46
  • @jason Continuing... If it is a concern, then make a `role` table that just has the role names and either have a FK from the intersection with employee (e.g. `divisionRoleEmployees`) - which would allow you to drop `divisionRole`. Alternatively, you could keep your `divisionRole` table and replace the `name` with a FK to the new `role` table. This latter approach doesn't reduce your table count, but it normalizes your role names. As I said, this is only helpful if your business rules state that role names exist across organizational groups (like divisions). – Joel Brown Mar 31 '17 at 19:48
  • Great, thanks for your help on this! I think I will use the recursive design you suggested – jason Apr 05 '17 at 13:58