Return to Answer

You may need to define indexes around friendships instead of doubling the number of rows:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE friendship
(
    friend_of INT NOT NULL,
    friend_to INT NOT NULL,
    PRIMARY KEY (friend_of,friend_to),
    UNIQUE KEY friend_to (friend_to,friend_of)
);

This way, you double the storage for indexes but not for the table data. As a result, this should be a 25% savings on diskspace. The MySQL Query Optimizer will choose perform index range scans only, which is why the concept of covering indexes works well here.

Here are some nice links on Covering Indexes:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

CAVEAT

If friendship is not mutual, you have the basis for another type of relationship : FOLLOWER

If friend_to is not a friend of friend_of, you can simply leave that relationship out of the table.

If you want to define relationships for all types, whether they are mutual or not, you could probably use the following table layout:

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');

From the relation table, you could arrange the relationship to be as follows

• Friends should be mutual
• Foes could be mutual or not
• Followers could be mutual or not
• The other relationships would be subject to interpretation (by the forgotten or forsaken or the recipient of revenge (fixed))
• Possibie relationships can be further extended

This should be more robust for all relationships, whether the relationship is mutual or not.

You may need to define indexes around friendships instead of doubling the number of rows:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE friendship
(
    friend_of INT NOT NULL,
    friend_to INT NOT NULL,
    PRIMARY KEY (friend_of,friend_to),
    UNIQUE KEY friend_to (friend_to,friend_of)
);

This way, you double the storage for indexes but not for the table data. As a result, this should be a 25% savings on diskspace. The MySQL Query Optimizer will choose perform index range scans only, which is why the concept of covering indexes works well here.

Here are some nice links on Covering Indexes:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

CAVEAT

If friendship is not mutual, you have the basis for another type of relationship : FOLLOWER

If friend_to is not a friend of friend_of, you can simply leave that relationship out of the table.

If you want to define relationships for all types, whether they are mutual or not, you could probably use the following table layout:

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');

From the relation table, you could arrange the relationships to include the following:

• Friends should be mutual
• Foes could be mutual or not
• Followers could be mutual or not
• The other relationships would be subject to interpretation (by the forgotten or forsaken or the recipient of revenge (fixed))
• Possibie relationships can be further extended

This should be more robust for all relationships, whether the relationship is mutual or not.

You may need to define indexes around friendships instead of doubling the number of rows:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE friendship
(
    friend_of INT NOT NULL,
    friend_to INT NOT NULL,
    PRIMARY KEY (friend_of,friend_to),
    UNIQUE KEY friend_to (friend_to,friend_of)
);

This way, you double the storage for indexes but not for the table data. As a result, this should be a 25% savings on diskspace. The MySQL Query Optimizer will choose perform index range scans only, which is why the concept of covering indexes works well here.

Here are some nice links on Covering Indexes:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

CAVEAT

If friendship is not mutual, you have the basis for another type of relationship : FOLLOWER

If friend_to is not a friend of friend_of, you can simply leave that relationship out of the table.

If you want to define relationships for all types, whether they are mutual or not, you could probably use the following table layout:

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)
);
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');

From the relation table, you could arrange the relationship to be as follows

• Friends should be mutual
• Foes could be mutual or not
• Followers could be mutual or not
• The other relationships would be subject to interpretation (by the fogotten or forsaken or the recipient of revenge (fixed))

This should be more robust for all relationships, whether the relationship is mutual or not.

You may need to define indexes around friendships instead of doubling the number of rows:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE friendship
(
    friend_of INT NOT NULL,
    friend_to INT NOT NULL,
    PRIMARY KEY (friend_of,friend_to),
    UNIQUE KEY friend_to (friend_to,friend_of)
);

This way, you double the storage for indexes but not for the table data. As a result, this should be a 25% savings on diskspace. The MySQL Query Optimizer will choose perform index range scans only, which is why the concept of covering indexes works well here.

Here are some nice links on Covering Indexes:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

CAVEAT

If friendship is not mutual, you have the basis for another type of relationship : FOLLOWER

If friend_to is not a friend of friend_of, you can simply leave that relationship out of the table.

If you want to define relationships for all types, whether they are mutual or not, you could probably use the following table layout:

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');

From the relation table, you could arrange the relationship to be as follows

• Friends should be mutual
• Foes could be mutual or not
• Followers could be mutual or not
• The other relationships would be subject to interpretation (by the forgotten or forsaken or the recipient of revenge (fixed))
• Possibie relationships can be further extended

This should be more robust for all relationships, whether the relationship is mutual or not.

You may need to define indexes around friendships instead of doubling the number of rows:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE friendship
(
    friend_of INT NOT NULL,
    friend_to INT NOT NULL,
    PRIMARY KEY (friend_of,friend_to),
    UNIQUE KEY friend_to (friend_to,friend_of)
);

This way, you double the storage for indexes but not for the table data. As a result, this should be a 25% savings on diskspace. The MySQL Query Optimizer will choose perform index range scans only, which is why the concept of covering indexes works well here.

Here are some nice links on Covering Indexes:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

CAVEAT

If friendship is not mutual, you have the basis for another type of relationship : FOLLOWER

If friend_to is not a friend of friend_of, you can simply leave that relationship out of the table. The table layout can now look more this:

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)
);
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');

This should be more robust for all relationships, whether the relationship is mutual or not.

You may need to define indexes around friendships instead of doubling the number of rows:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE friendship
(
    friend_of INT NOT NULL,
    friend_to INT NOT NULL,
    PRIMARY KEY (friend_of,friend_to),
    UNIQUE KEY friend_to (friend_to,friend_of)
);

This way, you double the storage for indexes but not for the table data. As a result, this should be a 25% savings on diskspace. The MySQL Query Optimizer will choose perform index range scans only, which is why the concept of covering indexes works well here.

Here are some nice links on Covering Indexes:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

CAVEAT

If friendship is not mutual, you have the basis for another type of relationship : FOLLOWER

If friend_to is not a friend of friend_of, you can simply leave that relationship out of the table.

If you want to define relationships for all types, whether they are mutual or not, you could probably use the following table layout:

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)
);
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');

From the relation table, you could arrange the relationship to be as follows

• Friends should be mutual
• Foes could be mutual or not
• Followers could be mutual or not
• The other relationships would be subject to interpretation (by the fogotten or forsaken or the recipient of revenge (fixed))

This should be more robust for all relationships, whether the relationship is mutual or not.