Friendship Table

checkeredmichael · #1 (**permalink**) 02-26-11, 09:34

Hey,
I'm having a go at creating my own social networking website and I am having a few problems getting the idea of how friends work, at the minute I just want the simple concept of someone views your profile, decides to add you as a friend and then the friend request need to be accepted. In my database I have...

id, Auto-increment, Primary Key
Profile_id1 //user who added the other user
profile_id2 //user to confirm the friendship
confirm // 0 for not yet accepted and 1 for accepted

I know you need to create a foreign key between both profile_id to the user's usernames on the profile, but on my database I don't have an option for a foreign key, so how else would I be able to make this foreign key? I have primary, unique and index. Is a unique key the same as a foreign key?

I need some help bad and I would appreciate any help. Also I have checked Facebook's schema which has helped me a bit.

r937 · #2 (**permalink**) 02-26-11, 10:21

don't use an auto_increment here

Code:

CREATE TABLE friendship
( profile_id1 INTEGER NOT NULL
, profile_id2  INTEGER NOT NULL
, PRIMARY KEY ( profile_id1, profile_id2 )  
, confirm TINYINT NOT NULL DEFAULT 0
);

what does "on my database I don't have an option for a foreign key" mean?

what database is this, mysql?

mysql does support foreign keys, but you have to use innodb tables

checkeredmichael · #3 (**permalink**) 02-28-11, 05:58

I'm using one.com for my hosting and they use mysql, but they don't have InnoDB, they have... MyISAM, MEMORY, BLACKHOLE, ARCHIVE, CSV, FEDERATED and MRG_MYISAM.

r937 · #4 (**permalink**) 02-28-11, 06:07

in that case you will just have to do without the foreign keys

note that this does not actually restrict your retrieval queries -- you simply join on the appropriate columns whether or not they are FOREIGN KEYs

the foreign keys would merely have enforced data integrity (so you could not make a friend relationship with a user that doesn't eist)

checkeredmichael · #5 (**permalink**) 02-28-11, 06:20

Ah okay, so that means I need to use join in php to join the two together? And I take it that will be the same for things like comments as well, because at the minute for my commenting I have...

id Auto Increment, primary key
profile_id
message
sent_from //has the users name instead of id
date_time

Should I change the sent_from to the users id and then join it? I know the way I have done it works, but I want to make sure everything I do is normalized properly.

r937 · #6 (**permalink**) 02-28-11, 10:44

Quote:

Originally Posted by checkeredmichael

Should I change the sent_from to the users id and then join it?

only if you allow users to change their names ~and~ you don't want to chase down all the tables where a change has to be made

with foreign keys, you just use ON UPDATE CASCADE

Internet Services

Web Development

Digital Marketing

Consumer Tech