On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:
>
> On 5/31/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> > On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > >
> > > I realize that I didn't explain the problem well enough (thus is the
> > > peril of being too close to it for a long time).
> > >
> > > I can have an (org <--> org) relationship or an (org <--> person)
> > > relationship as well as a (person <--> person) relationship. To make
> > > matters worse, a person or an org can have multiple relationships
> > > based a role. Here is an example.
> > >
> > > John is the Director of Forestry Dept.
> > > Jane is the Head of the Economics Dept.
> > > Joe is a Scientist at the Forestry Dept.
> > >
> > > rel1: John (Director: Forestry) has a joint project with Jane (Head:
>
> > > Economics)
> > > rel2: John (Director: Forestry) is the boss of Joe (Scientist:
> Forestry)
> > > rel3: John (Person: null) is a friend of Joe (Person: null)
> > > rel4: John (Person: null) goes to the same church as Jane (Person:
> null)
> > >
> > > and so on.
> > >
> > > Well, I really have three "vectors" per "node" -- person, role,
> > > organization. In each node, either person or organization can be
> null
> > > (obviously, role will never be null because every node or entity has
> a
> > > purpose). Each of these vectors can be defined by a finite number of
> > > meaningful properties -- for example, a person has a name, nickname,
>
> > > address, contact information, and so on. Even the role can have a
> > > level of importance -- a "secretary" at the Forestry department is
> not
> > > as important as the "Director," so the role can have a weight, and
> so
> > > on.
> > >
> > > This is the problem that I have to model. I am trying to devise a
> > > storage structure for something like this in SQLite.
> > >
> > >
> >
> >
> > Been there, done that. About 15 years ago.
>
> oh, how I hate you ;-)
>
> > We called the model CAPPR: Company, Address, Person, Phone, and
> Roles.
> ..
> >
> > So bottom line, you're going to need your ENTITY tables, and your
> > RELATIONSHIP tables, and your ROLE tables. You may also need "lookup
> > tables" to help enforce data cleanliness by reducing spelling errors
> or
> > similar issues.
> ..
>
> So, you are telling me that you did it, but I really want to know
> "how" to do it. I am sure this is not a unique problem I am trying to
> solve. I just am getting all knotted up trying to figure out a sane db
> structure to model this within so I can put data in and take data out
> without major contortions and yet have it be flexible and scalable for
> different kinds of relationships.
>
> So, the real CAPPR will be if you give me a few db design guidelines
> here.
>
> ;-)
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
>
The database was rather simple in pictures, but complex by the time you
added everything in to help the database help your program stay out of
trouble.
We chose to identify a person by various sub-fields, and since we were not
internationalization-aware, we didn't have to have our innocence sullied by
the different positions of family names, given names, surnames, and
alternate names based on who you were in relationship to. (Japanese and
asian language names, germanic, russian, spanish, etc.) So our sub-fields
were honorific (Sir, Don, The Honorable), title (Mr. Mrs. Dr. Reverend,
etc), first name, middle name, last name, and something we could never
decide on a name for: Post-name (the III, Junior, etc), and the degreed
position ( Ph.D., M.S.C.A, etc).
Data entry of this name could be by-field (preferred) or free text, which
the software then parsed into the separate fields for correction, and easy
searching later. A little bit of human effort here helped increase the
utility of their contact management activities later.
We identified address the same way the post office does: street number,
pre-direction, street name, street type, post-direction, suite/apartment
qualififier, suite/apartment number, city, state, zip, zip+4. The software
used address correction software certified for bulk mailing purposes with
the USPS.
Company name was almost the simplest. There was a freetext data entry
field, and then the actual database field. Abbreviations in the freetext
were expanded to the full name in the database field.
Phone numbers had country codes, area codes, telephone numbers,
extensions, passcodes, and special dialing instructions.
Roles were multi-level. Roles were identified by a unique ID number, and
then split into appropriateness (which relationship it could describe
[person-company, company-address, person-address, person-phone,
company-phone, address-phone), the directionality description (left to
right, right to left), and then into categories.
Next, since there were 4 entity tables, there were 6 relationship tables
whose foreign keys were the corresponding entries in the entity tables & the
role table, plus a free-text field for notes or comments.
Each column whose content was restricted to a list of standardized values
(the 50 states + canadian provinces, armed force theatres, territories, etc;
the list of titles or post-fix names; the directionality of streets, etc)
had a lookup table against which the column was validated. We found that
these lookup tables would feed directly into the user interface as a pop-up
list. Each lookup table had both a key entry, and a display value, and an
on-screen order so that more frequently used options were near the top of
the list.
Each and every table contained a set of columns describing who entered the
data, when it was entered, and when it was last modified (and by whom).
Each and every table had a journal table recording changes to the table by
recording previous-value tuples.
And last, but extremely important for debugging and auditability, there
was a transaction table which recorded the LOGICAL operations performed
against the databases. You could, in theory, replay the transaction table
against an empty database and your data would be re-created correctly, step
by step. We did, in practice, find bugs by replaying reported problems and
going "ah ha! They really did want to enter in a 25-letter last name."
So, while I haven't actually included SQL statements that build the
database, the description above should be sufficient to do so.
Hope that helps.
