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...
