On 5/31/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
P Kishor <[EMAIL PROTECTED]> wrote:
> I am modeling entities and their relationships to each other. A
> classic network digraph kind of stuff. The entities are organizations
> or persons. A person may be associated with none (org type
> "unassigned") or one organization, and, of course, an organization may
> have none or more persons in it.
>
> I started with
>
> CREATE TABLE orgs (
> org_id INTEGER PRIMARY KEY,
> .. bunch of org stuff ..
> );
>
> CREATE TABLE persons (
> person_id INTEGER PRIMARY KEY,
> org_id INTEGER -- FK to orgs(org_id)
> .. bunch of person stuff ..
> );
>
> CREATE TABLE relationships (
> rel_id INTEGER PRIMARY KEY,
> from_id INTEGER, -- person_id or org_id
> to_id INTEGER, -- person_id or org_id
> entity_type INTEGER DEFAULT 0, -- 0 is "org", 1 is "person"
> relationship TEXT
> );
Why do you feel you need this last table at all? All the information is
already available from persons.org_id.
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.
--
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]
-----------------------------------------------------------------------------
