Re: Data Model
Date: 30 Mar 2006 18:24:05 -0800
Message-ID: <1143771845.895571.239550_at_j33g2000cwa.googlegroups.com>
matthewdavis1980_at_hotmail.com wrote:
> I have a situation:
>
> There are many Judges.
> There are many Buildings.
> There are many Locations inside buildings( such as floors ).
> Each judge must reside at exactly one location.
> A staff member has a name, phone number, and email.
> Each judge has 6 staff members, one of each type: Clerk, Assistant
> Clerk, Coordinator, Bailiff, and Court Reporter.
>
> How would you model this scenario?
>
> Thanks, I'm looking forward to seeing the different results.
I'll give it whirl:
Table: Person // or perhaps "Employee"
person_id
person_role // judge, clerk, assistent, etc. (see note) 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 repeatative.
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 Assistent in another. For simplicity sake, I am ignoring that possibility.
This approach 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".
-T- Received on Fri Mar 31 2006 - 04:24:05 CEST