Social Networking Database

[Scotty2024]

I found an interesting article (Deconstructing Myspace.com Part 1 - Social Networking Database, btw there is not a Part 2) and after discovering that the basic structure behind these social networking sites is very simple, I wanted to experiment with it. I am having some issues with the queries suggested in the article. Here is what I did, please let me know if you see any errors.

 

CREATE TABLE IF NOT EXISTS Accounts (
ID varchar(255) NOT NULL default '',
DisplayName varchar(25) default NULL,
FirstName varchar(25) default NULL,
LastName varchar(25) default NULL,
Username varchar(25) default NULL,
Password varchar(255) default NULL,
PRIMARY KEY  ('ID')
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Friends (
AccountID varchar(255) NOT NULL default '',
FriendID varchar(255) NOT NULL default '',
PRIMARY KEY  ('AccountID','FriendID'),
CONSTRAINT FOREIGN KEY (AccountID, FriendID) REFERENCES Accounts (ID, ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

I was able to create two tables using similar code as above. I then wanted to play with the suggested queries. So I inserted some info like this.

INSERT INTO Accounts (ID) VALUES ('Person1');
INSERT INTO Accounts (ID) VALUES ('Person2');
INSERT INTO Accounts (ID) VALUES ('Person3');

 

Next I made Person1 and Person2 friends and Person3 request a friendship of Person1.

INSERT INTO Friends (AccountID,FriendID) VALUES ('Person1', 'Person2');
INSERT INTO Friends (AccountID,FriendID) VALUES ('Person2', 'Person1');
INSERT INTO Friends (AccountID,FriendID) VALUES ('Person3', 'Person1');

Following the first two inserts in the Friends table above, it is my understanding that for two people to be friends, there must be two entries in the table. The first has personA as the AccountID and personB as the FriendID, then the second is reversed with personB as the AccountID and personA as the FriendID. However, as above, there is not a friendship between Person1 and Person 3 yet. Is this correct?

 

So I tried to run some of the sample queries (i.e. Listing 2) and they failed. What is wrong with the following query? Apparently it should return all the people who have requested friendship with Person1 and are not friends yet. In this case Person3 should be returned I believe.

SELECT DISTINCT a.* FROM Accounts a INNER JOIN Friends f ON a.ID = f.AccountID WHERE f.FriendID = 'Person1'

 

Thanks for the help!

[Scotty2024]

Anyone know why the select query fails?

[fenway]

Failed how?