Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    "Friend of a friends" concept design

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Posts
    74

    How about Prisoners and Associates ...

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •