Hi.

I am in the process of planning a database for a social networking project and stumbled upon this url which is a (crude) reverse engineered guess at facebook's schema:

http://www.flickr.com/photos/ikhnaton2/533233247/

What is of interest to me is the notion of "Affiliations" and I am trying to fully understand how they work, technically speaking. Where I am somewhat confused is the NetworkID column in the FacebookGroups", "FacebookEvent", and "Affiliations" tables (NID in Affiliations). How are these network affiliations interconnected?

In my own project, I have a simple profile table:

CREATE TABLE [dbo].[Profiles](
    [profileid] [int] IDENTITY(1,1) NOT NULL,
    [userid] [uniqueidentifier] NOT NULL,
    [username] [varchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
    [applicationname] [varchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
    [isanonymous] [bit] NULL,
    [lastactivity] [datetime] NULL,
    [lastupdated] [datetime] NULL,
 CONSTRAINT [PK__Profiles__1DB06A4F] PRIMARY KEY CLUSTERED 
(
    [profileid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [PKProfiles] UNIQUE NONCLUSTERED 
(
    [username] ASC,
    [applicationname] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

One profile can have many affiliations. And one affiliation can have many profiles. And I would like to design it in such a way that relationships between affiliations tells me something about the associated profiles. In fact, based on the affiliations that users select, I would like to know how to infer as many things as possible about that person.

My question is, how should I be designing my network affiliation tables and how do they operate per my above requirements? A rough SQL schema would be appreciated in your response.

Thanks in advance...