I'm not a database programmer. I really don't plan on becoming one; I dabble. My skills and work lie elsewhere, and these keep me busy enough. Nevertheless, I find myself needing, and begging, for some quick help at times in an area in which I have no time or need to learn fluently. I have officially attempted some trials at building a couple of new tables for this, but to no avail. I do most of my learning in a hands-on setting, and will know how this particular schema works once I've received some direction (which is all I'm asking for, I think) and implemented it.
That said, the following was suggested by @RolandoMySQLDBA four years ago, and I've found it terrific for a project I'm building.
I'm hoping he will see this and that he, or someone, can help me extend this to include a table of "Organizations" along with a table of Organization rel_types, to be associated with any of the persons...
CREATE TABLE person ( person_id INT NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (person_id) ); CREATE TABLE relationship ( rel_id INT NOT NULL AUTO_INCREMENT, person_id1 INT NOT NULL, person_id2 INT NOT NULL, reltype_id TINYINT, PRIMARY KEY (rel_id), UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2), UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1), KEY has_relationship_to (person1_id,reltype_id), KEY has_relationship_by (person2_id,reltype_id) ); CREATE TABLE relation ( reltype_id TINYINT NOT NULL AUTO_INCREMENT, rel_name VARCHAR(20), PRIMARY KEY (reltype_id), UNIQUE KEY (rel_name) ); INSERT INTO relation (relation_name) VALUES ('friend'),('follower'),('foe'), ('forgotabout'),('forsaken'),('fixed');
I will be, and am, grateful to anyone who can assist me with this.