mysql - friendship database schema ideas comparison -

mysql - friendship database schema ideas comparison -

i want create community website, users can add together each other "friends". searched through net , found 2 ideas, , have 1 of own. want comparing give-and-take these ideas. here go:

idea 1: single row method in thought create friendship table these fields:

----------------------------- | id | user_id | friend_id | -----------------------------

in user_id , friend_id both foreign keys users table's id store friendship relation "a single" record. if users ids 15 , 30 friends then:

----------------------------- | id | user_id | friend_id | ----------------------------- | 1 | 15 | 30 | -----------------------------

in method, have search in both user_id , friend_id fields find friends of single user. illustration list of user 15 friends:

select friend_id friendship user_id=15 union select user_id friendship friend_id=15

idea 2: double row method in thought idea 1 create friendship table these fields:

----------------------------- | id | user_id | friend_id | -----------------------------

but instead of single record, store friendship relation 2 records. if users ids 15 , 30 friends then:

----------------------------- | id | user_id | friend_id | ----------------------------- | 1 | 15 | 30 | ----------------------------- | 2 | 30 | 15 | -----------------------------

in method, have search in in user_id field find friends of single user. illustration list of user 15 friends:

select friend_id friendship user_id=15

here queries much simpler, have info redundancy other friendship settings , fields (duplication). , crud cautions should used impact both rows in operation.

idea 3: hash field method in thought create friendship table these fields:

------------------------- | id | friendship_hash | -------------------------

here, store friendship relation in 1 record. have these definitions:

gets user id , create single user hash

single_user_hash = hash_this(id)

gets single user hash , returns user id

id = unhash_this(single_user_hash)

gets 2 user ids , returns double users hash

hash_these(id1, id2)=concat( hash_this(id1), hash_this(id2)) friendship_hash=hash_these(id1, id2)

gets user id , friendship hash, extracts , returns friend user id

find_friend(id1, friendship_hash)=unhash_this(replace(friendship_hash, hash_this(id1), "")) id2=find_friend(id1, friendship_hash)

if users ids 15 , 30 friends, set

hash_these(15, 30)

as friendship_hash.

one illustration of hash functions be:

hash_this(15)='m15m' unhash_this('m15m')=15 hash_these(15, 30)='m15mm30m' find_friend(15, 'm15m30m')=30

and table be:

------------------------- | id | friendship_hash | ------------------------- | 1 | m15mm30m | -------------------------

to find friends of user 15, have run query:

select find_friend(15, friendship_hash) friend_id friendship friendship_hash concat('%', hash_this(15), '%')

so here questions:

is thought (idea 3) usable? which 1 prefer? what pros , cons of each method?

thanks in advance.

this made interesting problem. query simple enough, design of intersection table has nice twist it. typically, intersection table designed this:

create table friends( friend1 int not null, friend2 int not null, constraint friends1_fk foreign key( friend1 ) references clients( id ), constraint friends2_fk foreign key( friend2 ) references clients( id ), constraint friends_pk primary key( friend1, friend2 ) );

defining pk both fields assures don't duplicate entries. in instance, friends( 2, 3 ) , friends( 3, 2 ) considered duplicates relationship equal in either direction. dictates of info integrity enforcement required possibility had eliminated. how?

the solution, do, turned out drop dead simple. add-on of 1 more constraint did it:

constraint friends_alt_dup_ck check( friend1 < friend2 )

this adds bit more complexity inserts, nil onerous. oh... query:

select c1.name name1, c2.name name2 friends f bring together clients c1 on c1.id in( f.friend1, f.friend2 ) bring together clients c2 on c2.id in( f.friend1, f.friend2 ) , c2.id <> c1.id c1.id = :friend_id;

sqlfiddle

i don't how elegant (and i'm great fan of elegance in code) has advantage of putting "friend of interest" in same column.

mysql sql database friendship