Court Room Schema Example

Here is an example from UseNet that may make for interesting discussion or schema design training example:

Problem Statement

I have a situation:

There are many Buildings.
There are many Locations inside buildings( such as floors ).
There are many Judges.
Each judge must reside at exactly one location.
Each judge has 6 staff members, one of each type: Clerk, Assistant Clerk, Coordinator, Bailiff, and Court Reporter.
A staff member has a name, phone number, and email.

How would you model this scenario?

Thanks, I'm looking forward to seeing the different results.

Solution 1

Table: Person // or perhaps "Employee" ------------- person_id person_role // judge, clerk, assistant, etc. (see notes) name phone etc...

table: Location --------------- locat_id building_ref // f.k. to Building table (not shown) locat_descript etc...

table: Locat_group ------------ locat_ref // f.k. to Location table person_ref

Now for the endless caveats:

Some might propose a different table per employee or person "type", such as a Judge table, Bailiff table, etc. I never liked such approaches. For one, people can and do change roles and it is easier to flip a role flag than delete and re-add. Plus, "types" often results in having to use verbose UNION queries to do the same thing to the different employee types, which is ugly, inefficient, and repetitive.

It is also possible in practice for a person to have multiple roles, such as after budget cuts. Further, a given person may be say a Clerk in one room and an Assistant in another. For simplicity sake, I am ignoring that possibility in the schemas; but to implement it, move the Role from the Person table to the Locat_group table. If a given person can have multiple roles in the same room, then the table content may resemble:

Locat_ref Person_ref Role --------------------------------- 2 7 Judge 2 22 Bailiff 2 22 Clerk

Here, person 22 serves as both the Bailiff and Clerk.

The above schema does not enforce six and only six positions per location (room), at least not without extra logic. If the goal is to keep things flexible, it is often a bad idea to hard-wire a fixed set of slots into schemas. Many-to-many tables, such as the Locat_group table above allows such flexibility for future changes. However it may complicate the immediate needs to build such flexibility into the system before it is actually needed.

If you want to better enforce the "six" rule and avoid many-to-many tables, then perhaps make a Courtroom table:

 table: Courtroom
 -----------
 crt_room_ID
 crt_descript
 building_ref // f.k. to Building table (not shown)
 judge_ref    // f.k. to Person table
 clerk_ref    // f.k. to Person table
 assist_ref   // etc...
 coordntr_ref
 bailiff_ref
 reporter_ref

In such a case, we may not need the "role" column in the Person table because having an ID in the slots of the Courtroom table supplies that info instead. Further, it allows one to be different roles in different rooms. However, keep the "role" column if you don't want that feature.

And perhaps we don't need the Location table either, since the Courtroom table can serve that purpose instead. However, it cannot be used to track storage rooms, administrative offices, etc. It creates the same kinds of "one-table-per-type" problems that the employee types suggestion above did.

But if your system is to only focus on courtrooms, then other types of rooms may not be your concern. You may want to bring up the issue of consolidation with other room-tracking systems, though. It will show them that you are forward-thinking. Just don't insist on it if the customer is not interested. A mild suggestion is sufficient.

In such designs, one is often faced with the tradeoff of short-term versus long-term. Satisfying immediate needs may make development simpler up front, but in the longer run somebody 12 years down the road will cuss their heads off at you if you take the short road and stick them with the kludgy patchwork needed to make static slots or category-specific tables into a more flexible system. However, you may be long gone and don't care anymore. There is something to be said for "time discounting" from the financial world, where one knocks off about 15% per year of the cost of a given decision. In other words, "discount, but don't entirely ignore the future".

(The originator of the problem has since confirmed that the complexity has increased and the more flexible approaches, such as many-to-many tables, proved useful.)

-- Top