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. At the time I worked for Homes and Land Publishing in Tallahassee, and we built customizable software for the associate publishers who owned the magazine franchises in various areas. This software was used in their offices to manage their contacts with brokerages, advertisers, and so on. We called the model CAPPR: Company, Address, Person, Phone, and Roles. And it was a many-to-many network between each of those entity types: COMPANY had zero or more ADDRESSES. PERSON had zero or more ADDRESSES. COMPANY (or PERSON) had zero or more PHONE numbers, each with a different purpose. COMPANY had zero or more PERSONS known to be associated with them, each with one or more ROLES. It worked very, very well, as it modeled the real world accurately. One of the interesting things it let them determine was who gave out non-unique addresses for different companies, helping them to reduce fraud and to "know your customer" better. (This was before the advent of well-known databases of private mailbox companies like MAILBOXES ETC.) 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. In our case we built our model on Oracle RDBMS with full referential integrity enforced, which prevented a boatload of common programming errors from messing up the relationships between persons, companies, addresses, and phone numbers by deleting records that were still in use. I don't recall if SQLite enforces referential integrity, or merely accepts the syntax against a future update that will. --andy
