Database Design Help Required

Hi

I am working on a community site that pretty much works like anyother community site like orkut or myspace..I have few doubts for which i badly need your help.. if you can point me to some usefully links, articles, pdf or your suggestions..i will surely be obiliged.

THE application i am talking about willl be invite only.. and will let the users grow there network of friends... there will be other data associated with each userid like the profile,bookmarks etc etc.. , also there will be aurthorisation based on who are the members friends are who are not...

My problem.. database design

though i am planning ot user MS SQLSERVER 2005 ,, i have not finalised yet.. I want to make up my mind on how to structure the database..also,,if you have seen Orkut.com when you visit a cirten persons profile it shows (trhu a breadcrum like view) how you are connected.. ie.. thru what friend of yours you are connected...

I want to know ,,what kind of mapping is used here... how can i achive that without sacrifising performance,, coz surely thease kind of applications are to be build for VERY LARGE USER BASE...

Please suggest ...I am fighting my war alone..but i am determind.. you can help though. :)

If you go with Sql 2005, you can retreive breadcrumbs in a single query very easy and efficiently.

Say I have a Catalog with the following Categories:

CategoryIDNameParentID0Home-11Aquatic Supplies019Filters120Canister Filters19

If I use the following query against my Categories Table:

WITH Navigation (CategoryID, ParentID, Name, Sequence) AS ( SELECT CategoryID, ParentID, Name, 0 AS Sequence FROM Categories WHERE CategoryID = @.CategoryID AND Disabled = 0 UNION ALL SELECT c.CategoryID, c.ParentID, c.Name, n.Sequence + 1 AS Sequence FROM Categories c INNER JOIN Navigation n ON c.CategoryID = n.ParentID) SELECT CategoryID, Name, ParentID FROM Navigation

WHERE @.CategoryID = 20 and Navigation is a derived table, the results come back like this

CategoryIDNameParentID20Canister Filters1919Filters11Aquatic Supplies00Home-1

This makes it very easy to build breadcrumbs (you can tack on an ORDER BY CategoryID to flip the results).

So if you have a table of related data, replacing the above data:

UserIDNameFriendID0Bob-11Tom019Jerry120John19

And you are looking at John's profile, you can run the above query and trace this back to you "Bob." It's probably a little more in depth than that because not everyone in the table will end up back to a single "Home" friend, but you can probably modify the query statement to know exactly where to stop. I hope this gives you a start.|||

thanks, surely thats a great start.. will get back to this post, once I get my coding done, to post what i did .

thanks again.