Database design ala MySpace

[nloding]

I'm sure this has been discussed in the past, but I can't find any old threads.  I can't find much Googling either, just some ColdFusion junk that I wasn't interested in.  Here's the question(s):

If one were designing a social networking site, what is the best approach?

a) Each user gets their own tables (i.e., 1 blog table per user, 1 profile table per user)
b) Every X users, a new table is created (x = 10, so every 10 users is a new table for each blog, profile, etc.)
c) One HUGE table for each category with each user as a row.
d) Some combination of both -- like, one giant table for all profiles, but each user has their own blog table.

I'm in the beginning stages of designing a site that is actually quite complicated.  Here's a brief outline:

Each user has a unique login -- each login is linked to several "characters" -- each character has their own profile like MySpace, Facebook, Friendster, etc -- each profile has friends, blogs, photos, calendars, etc.  At first, this site won't have too many users, but we are expecting up to 10,000.

So ... what do you all think?

You guys are very helpful, thank you in advance for everything!

--Nathan

[newb]

oh god, another social networking site...sigh...

[nloding]

Heh, actually not.  It's NOTHING like MySpace or Friendster in it's actual application.  It's a site devoted to a certain clique of people, all of whom share one thing in common.  Picture a MySpace only for Star Wars freaks -- it'd be something like that.  And it's point isn't really "social networking" ...

But regardless, the database design has the same needs at MySpace or Friendster ... hence my question.

[neylitalo]

I'm getting heartburn just thinking about the poor server. Literally. Database design rule #82: Never dynamically create tables. Emphasis on never.

The basic idea: create a table user, a table blog, a table profile.

Each time a user is created, add a row to user. Add a row to profile where one of the fields in profile is an ID that matches up with the ID in user. Therefore, the content in profile is linked to the user.

Each time a user makes an entry in their blog, add a row in blog with the information, and link an ID field in blog to the ID field in user.

And that, my friend, is the basic idea.

[448191]

[quote author=neylitalo link=topic=109152.msg439878#msg439878 date=1159004184]
...Database design rule #82:....[/quote]

LOL, is that a random number or from a book?  :P

Me hungry fro resources. :P

[nloding]

Alright, I'll do some digging to foreign keys, which is something I know about but haven't had to utilize yet.

Now, is there an sort of organization that should go into, say, the blog end of it?  Obviously the user can have as many blog entries they want -- and I assume each entry is it own row in the blog table, with the user ID being a key.  Just randomly add the rows down the table as the users enter data?

[neylitalo]

[quote author=448191 link=topic=109152.msg439895#msg439895 date=1159007795]
[quote author=neylitalo link=topic=109152.msg439878#msg439878 date=1159004184]
...Database design rule #82:....[/quote]

LOL, is that a random number or from a book?  :P[/quote]

No, I just kinda made it up, no books involved. :)

[Barand]

#82? I'm surprised it's that far down the list.

[newb]

[quote author=Barand link=topic=109152.msg441915#msg441915 date=1159296685]
#82? I'm surprised it's that far down the list.
[/quote]

im not.

[neylitalo]

oh, come on Barand... it was a joke and you know it. :'(

On a similar-but-unrelated topic, I just heard there's candy down the hall.