Re: Storing data and code in a Db with LISP-like interface

From: Neo <neo55592_at_hotmail.com>
Date: 18 Apr 2006 18:36:57 -0700
Message-ID: <1145410617.182976.267600_at_e56g2000cwe.googlegroups.com>

> > 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_CourtReporter
T_Phone, T_EMail,
T_Name

And the equivalent of following hierarchy tables:

T_HasHierarchy - 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 relationships with not only different things, but to the same thing.

Thus the exp db handles "types", "roles" and other relationships 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? Received on Wed Apr 19 2006 - 03:36:57 CEST