2

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.

1
  • Check "The Universal Data Model Resource Workbook" by Len Silverston. In particular you need some implementation of Party-Role-Relationships model.
    – a1ex07
    Sep 20, 2016 at 14:39

1 Answer 1

1

If you want to keep things simple, it may be enough to add organization(organization_id PK, ...) table and person_organization_relationship (person_org_relationship_id (PK) , person_id,organization_id ,reltype_id , date_from, date_thru). Depends on requirements you may or may not add unique constraint, on , for instance, (person_id, organization_id,reltype_id). I would reuse the same relation table which is used for describing relationships between persons. In many cases such model will be working just fine.

If more general model is needed, concept of Party can be added. Party can be a person, or organization, or something else that interacts with the system . For instance if RDBMS supports enums, you can have

PARTY (party_id PK, party_type enum('person','organization'), [common attributes]);

Then you either add 2 tables, PERSON and ORGANIZATION (which both have party_id as a primary key and foreign key to PARTY) and store their attributes separately, or store all attributes in PARTY table.

Concept of Party lets you store any type of relationship between person and organization,or 2 organizations , or 2 persons in one table. Moving forward, it'll make other things easier to implement. For example, various contact information such as address(physical or electronic), phone, etc can stored in one place . On the other hands, it has its price. The more universal model you are implementing, the more time you'll need to spend in the beginning.

So I'd recommend to use simplest model that satisfies requirements .

Some notes regarding current physical implementation of relationship table:

     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)
  1. It's better not to have the least selective column (reltype_id) as a leading column in outer_affinity and inner_affinity
  2. For enforcing uniqueness it's enough to have one of outer_affinity, inner_affinity
  3. I'd start with just two indexes UNIQUE KEY UQ_relationship_person_1_person_2 (person_id1, person_id2, reltype_id) and INDEX IDX_relationship_person_2(person_id2) (depends on typical queries it might be useful to person_id1 as second column to this index).
  4. The table seems to miss date_from (and maybe date_thru) attribute .
4
  • regarding PARTY: "Concept of Party lets you store any type of relationship between person and organization,or 2 organizations , or 2 persons in one table..."
    – glenn nall
    Sep 20, 2016 at 18:15
  • I mean when relationships table has party_id1, party_id2 , not person_id1, person_id2 , then for instance you can put first party of type person, second of type organization, and relationship type as "employee of" ; if you put put 2 parties of type person , relationship type can be "follower of"; if both parties are organizations, relationship type may be "whitelable of " , and so on. So you don't need 2 or 3 tables - one for person-to-person, another for person-to-organization, and one more for organization-to-organization relationship
    – a1ex07
    Sep 20, 2016 at 18:28
  • "Concept of Party lets you store any type of relationship between person and organization, or 2 organizations , or 2 persons in one table..." - What about a chain of 3 or 4 persons/orgs? Should I look into PARTY for a multi-level UL output such as : ul-li John (knows) » ul-li Steve (who knows) » ul-li Henry (who knows) » ul-li Alan, AND ul-li Steve (knows) » ul-li Tom (who knows) » ul-li John... make sense? interspersed are Organizations, the structure of all of this i've built in html, but I'm open to a different structure if necessary. thanks for your help so much...
    – glenn nall
    Sep 20, 2016 at 18:30
  • Oh, I see - PARTY isn't a particular function, it's the overall relationship concept you're suggesting. that makes sense. it's all about the queries to extract the data that i want, anyway, right? cool thanks
    – glenn nall
    Sep 20, 2016 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.