Help with my database design?

September 21, 2011 1:26 PM

How should I depict our complicated organizational structure in my database design?

I am a member of a team that teaches a series of classes for our company and some of our company's partners. In addition to the classes, we also do one-on-one consultation and coaching. We want to build a database to help us track our reach, as well as help us keep track of our scheduled consultations and when a student needs follow-up.

We want the students to be associated with the organizations they work for. These might be either within our company, in which case we would want to track the office, division, branch, and team name, or outside our company, in which case we would want to track the organization name and type.

So far it seems like the best solution is to have two different tables:

Organization (within our company)
-ID
-Team Name
-Office_id (linked to a table of offices)
-Division_id (linked to a table of divisions)
-Branch_id (linked to a table of branches)

Organization (outside our company)
-ID
-Name
-Type_id (linked to a table listing the different types of external organizations)

Each office contains multiple divisions, each division contains multiple branches, each branch contains multiple teams. The "team" level is the lowest level we are tracking.

Each student's record will connect them with a single organization ID, which will be from either of the two organization tables. Ideally, when we are using the DB, we would fill in a form where we could pick whether they were internal or external, then (for instance) pick the office and it would then automatically allow us to pick only from the divisions that belonged to that office, and so on down the line. (This will be done in Access, if it makes a difference.)

We want to be able to run reports on any level of the company- we often get questions like, "has anyone else in my division had this training?" and we would like to have a good way to answer that.

So my question is, how is the best way to design the tables and link them up so that this will all work?

posted by oblique red to Computers & Internet (5 answers total)

I'd have Organization:
OrganizationID
Name
TypeID

And Organization_InternalDetails:
Organization_InternalDetailsID
OrganizationID (Foreign Key referencing OrganizationID)
Office_ID
Division_ID
Branch_ID

Then SELECT FROM Organization o LEFT JOIN Organization_InternalDetails oid ON o.OrganizationID = oid.OrganizationID

You can then find external orgs by SELECTing WHERE Office_ID IS NULL on the joined set of tables.
posted by bfranklin at 1:45 PM on September 21, 2011

Best answer: If each student is going to be part of an office which is part of a branch which is part of a division which is part of an organization, you probably want to just tie them directly to the office instead of the larger organization.

Also you can probably just have one organization table with internal/external as a flag (or you can include 'internal' as a type)

So you'd have a student table, a branch table, an office table, a division table and an organization table with many to one relationships for all of them.

Student
id
name
office_id

Office
id
name
branch_id

Branch
id
name
Division_id

Division
id
name
Org_id

Organization
id
name
type_id

Or I guess you could also use office, branch, division, and organizations in all the same table and use recursion to make some organizations belong to other organization, but that might get too complicated.
posted by empath at 1:46 PM on September 21, 2011

Best answer: I largely agree with empath. By making the Office, Division, and Branch all settable on the Organization table, you are setting the table up to hold symantically invalid information. That is, you can legally specify that an Organization works in Office 1 and Division 50, when Office 1 only actually houses Divisions 1-3.

I think the following would work. (I read the question several times to try to get the hierarchy right; I hope this reflects it.)

Office
-- id
-- name

Division ( Many Divisions to one Office )
-- id
-- name
-- office_id

Branch ( Many Branches to one Division )
-- id
-- name
-- division_id

Organization ( Many Organizations to one Branch, and/or many external Organizations with no Branch specified.)
-- id
-- name
-- branch_id (nullable, in case of external organization)

Student ( Many Students to one Organization)
-- id
-- name
-- organization_id
posted by JohnFredra at 1:55 PM on September 21, 2011

Yeah i wasn't clear on the heirarchy, but obv you go from top to bottom..
posted by empath at 2:00 PM on September 21, 2011

Response by poster: Yes! That will work! By making "internal" a type and then letting each level just have the ID of the level directly above it, I can have just one "organization" table with an ID, and everything becomes a million times simpler.

I love you, internets!
posted by oblique red at 2:26 PM on September 21, 2011