Does a person ever belong to more than one organization? If so then use the following: Create table orgs ( org_id integer, org stuff); Create table Persons(person_id, person stuff); Create table persons_orgs(person_id, orig_id); The persons_orgs is a mapping table that allows one to place a person in one or more organizations. Orgs <-> persons_orgs <-> persons But if a person never belongs to more than one org: then just A special org_id = 0 could be created called "unassigned".. Thus, every person is then in an organization. Create table orgs ( org_id integer, org stuff); Create table Persons(person_id, org_id, person stuff); P Kishor <[EMAIL PROTECTED]> wrote: Not a SQLite-specific question per se, but a (SQLite) db design question.
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 ); But the above doesn't seem very good to me because of the from_id and to_id that are ambiguous depending on the entity_type. I am mixing entity types in the same table. Alternatively, I could have a single entities table which holds both orgs and persons CREATE TABLE entities ( entity_id INTEGER PRIMARY KEY, .. bunch of org stuff if org .. .. bunch of person stuff if person .. ); and then follow with just the relationships table. But that would be mixing two different types of entities in the same table, would leave a lot empty slots depending on the entity type, and would have to have some kind of recursive relation (persons belonging to orgs). Ultimately, I want to grab the relationships out and model them using a network digraph display algorigthm, but that is another problem. Many thanks in advance for guidance. -- 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] -----------------------------------------------------------------------------
