Thread: Database design advice needed

Hello people
Sorry for the non-descriptive title

I need to make something like Facebook's Wall.
So there can be 2 kind of posts - normal text post and image post.

So should i keep all info in 1 table like

-WallPosts
PostId
UserId
PostType (1 for Text post, 2 for Image post - for easy sorting)
DateTime
Comment/Text
ImageURL

Or try having them in 2 tables

-WallPosts
PostId
UserId
DateTime
PostType

-WallPostDetails
Id
PostId
Text/Comment
ImageURL

What will be more efficient if say large number of records are there?
Also not to mention my query writing capabilities

Or any other approach?

.NET 2.0
SQL Server 2000


Thanx
Micky

I'd say store them in one table. I can't see much advantage in storing them in seperate tables.

I presume you could have a post with image and text? How would this be handled with 2 tables?

Probably best to get the opinion of one of our database experts though...

Thanx RR, I am also thinking of 1 table only......... cant really see or foresee any benefit of 2 tables!

Originally Posted by micky

Thanx RR, I am also thinking of 1 table only......... cant really see or foresee any benefit of 2 tables!

I guess the only advantage would be if you'd want to run queries seperately on the posts and posts data. You'd probably see a small performance advantage. Can't really see any circumstance where you'd query one table without the other though. In which case you may as well store all the info in one table. I would also assume the relationship between the tables would be 1 to 1. Ie each record in one table would have a corresponding entry in the other table. As I understand it, this sort of design should be avoided in most circumstances.

Well said RR

Cant see where just any one table might be called.......... so more reasons to have 1 table.

I see this as table structure

-WallPosts
PostId
UserId
FriendId (If any friend posts on my wall)
PostType (1 for Text post, 2 for Image post, may be more)
DateTime
Comment/Text
Details (Image URL, etc)

Also, i think i should save entries like - "Micky is friends with RR"........... also in this table only.
RR's id will saved in field "Details"

I think you're maybe referring to the Facebook "News Feed" rather than the wall. I think they are 2 different things. I don't know how Facebook store the xxx is friends with yyy, as the yyy is a link to their profile. If they store the whole feed in one table then I presume they store the link as facebook markup.

So, any action that posts to the feed; Making friends, Someone posting on your wall etc. would make an entry in the "news feed" table as it will appear and also as the item in the specific table. ie a wall post in the "wall" table, a friends link in the "friends" table.

Don't know if the "news feed" can go right back to day 1, ie when you joined. That would be a massive table.

"News Feed" shows all your entries and also friends entries.

But this "xxx is friends with yyy" is also shown Wall....... and to link back to friend's profile, i said of storing the id of friend in field "Details"

I am not even close to thinking about how i will be able to implement "News Feed"

Originally Posted by micky

"News Feed" shows all your entries and also friends entries.

But this "xxx is friends with yyy" is also shown Wall....... and to link back to friend's profile, i said of storing the id of friend in field "Details"

Oh, is it...Not a huge facebook user... I guess store a similar entry in the "wall" table then. Like I said above, I'd guess FB store the user link as markup. They have there own markup language. So, say they had something like <fb:user id="12" /> (Don't know is this a real FB markup!) the markup engine would rewrite this to <a href="http://www.facebook.com/user.php?id=12">this user</a>. Also prevents any potential injection attacks as you can't use HTML in any posts, only FBML.

Originally Posted by micky

I am not even close to thinking about how i will be able to implement "News Feed"

Don't know if what I suggested would work. The only other thing I can think of is you'd have to have some sort of mirrored table design with the same fields in each and then use a union to join them all together. Probably get pretty messy.

Hope that helps..

ha...... never knew abt FB markup language

and yes.... "messy" is the word here

Thanx for inputs RR......... would be nice if some more people pitch in

Originally Posted by micky

ha...... never knew abt FB markup language

Here you go....Facebook Markup Language (FBML) - Facebook Developers

Originally Posted by micky

Thanx for inputs RR......... would be nice if some more people pitch in

Think you'll have to wait a little longer for the US to come fully onboard...