database question

[Jas123]

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

 

[angelwatt]

Here's a simple DB layout for something like this.

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
);

 

[Jas123]

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?

 

[notnek]

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.

 

[BertyBoy]

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.