> > Neo: Judges, Bailiffs, etc in Court Building Example
>
> TopMind: http://www.c2.com/cgi/wiki?CourtRoomSchemaExample
I will make some general comments based on the assumption that the RMDB
solution will be similar to exp db solution (except in terms of
constraints, which rmdb can handle much better). Currently the simpler
RM schema consists of approximately the following tables (I have
modified some names slightly to make the comparison easier, if this is
disruptive, let me know).
T_Person (personID, role, name, phone#, email)
T_Building (bldgID, name)
T_Location (locID, name)
T_Person_Loc_Map (personID, locID)
While the exp db doesn't have "tables" (unless modelled), it is similar
to having the following tables:
T_Building, T_Floor, T_Room,
T_Person, T_Judge, T_StaffMember, T_Clerk,
T_Assistant, T_Coordinator, T_Bailiff, T_CourtRe****ter
T_Phone, T_EMail,
T_Name
And the equivalent of following hierarchy tables:
T_Ha****erarchy - This allows buildings to have floors to have room and
any of those things to have a person and so on (basically anything can
have/contain/bePartOf something else as in a BOM).
T_StaffMemberHierarchy - This allows a judge to have staff members and
any of them to have their own staff members and so on. Note: there is
an error in my example, "Clark -assistant- Ashley" should have been
"Clark -staffMember- Ashley".
Also, since the exp db's solution allows each thing to have 0-to-many
"attributes", and each attribute to have 0-to-many "values" to avoid
NULLs, to do the same in RM one would need the following tables just
for persons' emails' addresses.
T_Person_Email_Map
T_Email_Address_Map
or some how all of them could be generalized to:
T_Thing, T_Attrib, T_Value
T_Thing_Attrib_Map
T_Attrib_Value_Map
> Website: The [rmdb] schema does not enforce six ...
Since the exp db doesn't enforce any app-specific constraints, the rmdb
solution shouldn't either, to keep the comparison simple.
> Website: 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 [above] schemas; but to implement it, [move the
[field] Role from the T_Person to new additional tables T_Group and
T_Loc_Group_Map(personID, groupID)]
First, think in general where any combination of the following is
possible:
1) a thing can have 0-to-many types.
2) a thing can have 0-to-many roles with not only different things, but
to the same thing.
Because the exp db treats types and roles in a similar manner, there is
only one case to handle:
1) a thing can have 0-to-many relation****ps with not only different
things, but to the same thing.
Thus the exp db handles "types", "roles" and other relation****ps in
exactly the same manner, where as in RM, typing, roles and their
cardinality affect implementation, and if changed, propogate a wave of
changes thru the schema, data, queries, code, etc.
When one starts dealing with specific types of things (ie persons),
some combination of typing/roles/cardinalities are more likely than
others and some not possible or we haven't experienced it yet. These
are app-specific constraints which the exp db engine doesn't implement.
Consider following examples:
1) Judy is a person.
2) Judy is a person, judge and dentist.
3) Clark's clerk Judge Judy (just on Clark's birthday).
4) Harris County judge is Judy.
5) Judy is a retired judge.
6) Judge Judy clerk Judge Judy (Judge was her own clerk when Clark was
absent).
7) CourtHouse1 receptionist Judge Judy (Judy will fill any role!)
My selection of types and roles of various persons (app-specific
constraints) were probably incorrect; however, its primary goal was to
demo flexibility. Limiting all person to one classification and one
role wouldn't demo it. I am not sure how you want to handle this as you
realize this would require more tables and joins.
> Website: 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.
> (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.)
Along those lines, it was the process of repeatedly having to update
schema, data, queries, code and GUI to handle new application
requirements that made me wonder if there existed a schema so general
that it would handle anything.
Currently the gap between the initial rmdb schema and that required is
enough to prevent a meaningul comparison. And there is still more which
I haven't expressed and isn't evident. How do you want to proceed?
|
