Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Friendship Table

    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.

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

  3. #3
    Join Date
    Feb 2011
    Posts
    3
    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.

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

  5. #5
    Join Date
    Feb 2011
    Posts
    3
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by checkeredmichael View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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