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/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
