Jump to content


Photo

Database design ala MySpace


  • Please log in to reply
9 replies to this topic

#1 nloding

nloding
  • Members
  • PipPipPip
  • Advanced Member
  • 321 posts

Posted 23 September 2006 - 01:22 AM

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

#2 newb

newb
  • Members
  • PipPipPip
  • Advanced Member
  • 454 posts

Posted 23 September 2006 - 02:16 AM

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

#3 nloding

nloding
  • Members
  • PipPipPip
  • Advanced Member
  • 321 posts

Posted 23 September 2006 - 03:36 AM

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.

#4 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 23 September 2006 - 09:36 AM

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.
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#5 448191

448191
  • Staff Alumni
  • Advanced Member
  • 3,545 posts
  • LocationNetherlands

Posted 23 September 2006 - 10:36 AM

...Database design rule #82:....


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

Me hungry fro resources. :P

#6 nloding

nloding
  • Members
  • PipPipPip
  • Advanced Member
  • 321 posts

Posted 23 September 2006 - 12:57 PM

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?

#7 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 24 September 2006 - 06:11 PM

...Database design rule #82:....


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


No, I just kinda made it up, no books involved. :)
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 26 September 2006 - 06:51 PM

#82? I'm surprised it's that far down the list.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 newb

newb
  • Members
  • PipPipPip
  • Advanced Member
  • 454 posts

Posted 26 September 2006 - 07:11 PM

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


im not.

#10 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 26 September 2006 - 07:53 PM

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.
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users