Hey all,

I'm currently designing a database in MYSQL as a learning experience, but I'm stuck at designing a function similar to facebook's "Friend of a friend of a friend" design (What's the correct terminology?). Where I am connected to Jim Bob from New York through Mary Ann in Washington DC. Please advise. Any help is much appreciated! Thanks

terminology? i would say "friend of a friend" is descriptive enough

you'll need a table of people, yes? with name, age, hatsize, etc.

and you'll need another table of relationships, to relate person1 to person2, so this table will have two columns, the ids of the two people involved

load up some data and see if you can figure out the sql necessary to retrieve the related names

This Data Model on the Database Answers web site might provide some insights :-
http://www.databaseanswers.org/data_...ners/index.htm

HTH

B.Dimple
Senior DBA

the operative word being might

i would argue vehemently against the columns prison_staff_id and prisoner_offence_id

sometimes, a totally superfluous key, like those two, is argued as being necessary as the parent key of a child of the association

but notice those two don't have child tables

the one association table that does have a child table, prisoner_associates, why, it uses (correctly, in my opinion), a compound natural key

who'd've thunk it! you got to take those designs what you find on them internets with a grain of salt!

true.

IMO, an intersection table has no need of a surrogate primary key - the compound Primary Key serves perfectly. (after all, even iIF you added an unnecessary surrogate PK, you would STILL need to define a unique key for the two foreign keys)