Social Networking & Network Affiliations

Question

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

Answer 1

First off, it's an object schema not a database schema. Second, it's not showing everything, far from complete. Look at the Schools class (not table) notice the relations, high school 1 and 2 and school list. Do you see properties in the Profile class to hold those "Foreign Keys".

Your question cannot be answered accurately

Second, why care how facebook does it? Third Normal Form isn't a black art? There's no Tome of Lost and Gone Forever knowledge you're not privy to.

Do you want a many to many relationship between affiliations and profiles? Or Not? Are you attempting to uncover interdependencies between affiliations?

Why not just ask us specific questions about how to design what YOU want to do?

EDIT

One profile can have many affiliations. And one affiliation can have many profiles.

This is a many-to-many relationship.

In a physical database you need a many-many mapping table.

At it's simplest it contains the PK from both tables and those columns form that table's PK.

So now just add columns to the mapping table.

Start_date => date the profile added that affiliation

End_Date => essentially a logical delete

What else do you want to know about the Relationship.

Think about these objects 1. Video 2. Renter 3. Store

There's a many to many to many that was can describe as a RENTAL.

The VideoID, RenterID, StoreID,

What else do you want to know about a "RENTAL"

1. Date (Checked out)
2. Returned Date
3. Due Date
4. Discount (say it's rent 3 get 1 free and this is the fourth or something)