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] -----------------------------------------------------------------------------
