database question

Discussion in 'Web Design and Development' started by Jas123, Sep 8, 2009.

  1. Jas123 macrumors member

    Joined:
    Apr 1, 2008
    #1
    What's the best way to design a database to keep track of friends or subscriptions. So for instance you could see all the people subscribed to a particular "thing." Thanks for any help
     
  2. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #2
    Here's a simple DB layout for something like this.
    Code:
    CREATE TABLE friends (
     id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
     name VARCHAR(64) NOT NULL,
     email VARCHAR(64)
    );
    CREATE TABLE subscriptions (
     id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
     name VARCHAR(64) NOT NULL
    );
    CREATE TABLE subscribed (
     fid INT NOT NULL,
     sid INT NOT NULL
    );
     
  3. Jas123 thread starter macrumors member

    Joined:
    Apr 1, 2008
    #3
    Should I set up my third table like that without any keys? Doesn't this set up it violate normalization? Or does that not apply to a key table?
     
  4. notnek macrumors 6502

    notnek

    Joined:
    Oct 25, 2007
    #4
    the 3rd table in angelwatt's example is the link between user and subscription. if you wanted, you could add an ID for each linked subscription but it's somewhat of an overkill. his example should work just fine.
     
  5. BertyBoy macrumors 6502

    Joined:
    Feb 1, 2009
    #5
    The two fields in the third table are a composite key, if it makes you feel any better. So you may also want a unique index on those two fields. Or rather two unique indexes, with the keys in (fid,sid) order and (sid,fid) order.
    You may also want referential integrity from the third table back to each of the first two tables. I'm sure MySQL provides this, but Oracle is my thing. Don't know about the others. This would prevent you adding records in the third table if the friend or subscription didn't exist. It would also prevent you deleting friends or subscriptions until all the entries in the third table for that friend / subscription had been deleted.
     

Share This Page