Social Feed Part 1 – The Database
This is the first part in a series of articles focused on building a web application with similar features to that of FriendFeed and Twitter.
So as I mentioned yesterday I am going to have a shot at building a web application similar to FriendFeed and Twitter, it’s going to be called Wandering Worlds Social Feed or wwSF for short.
To kick things off I’m going to talk about the database. wwSF will be a very data heavy project and we need to make sure that the data going into the database is put there in a logical way.
Here’s the first run through of database tables I believe I’ll require.
Database Tables
User – This table will hold all user information.
Content – This table will hold all content, both created and aggregated from other sources.
ContentType -This table will store a unique identifier for the services we will be aggregating (Twitter, Flickr etc) and also types of content postable from the application (videos, photos, text)
Relationships – This table will hold the relationship (friend) information between users.
Comments – This table will hold all comments, comments can be posted to all kinds of content.
Likes – This table will hold all likes, all kinds of content can be liked.
AggregationData – This table will hold all aggregated user URLs, last time the data had been refreshed etc/
Here’s a little diagram that I modelled in Visio to give a more graphical representation of the relationships between tables.
Okay, so there you have what I believe the database should look like for wwSF. Remember that this isn’t going to be some crazy fully featured web application, at first I just want to get the basics down and then possibly look to build upon them in the future.
If you have any suggestions for ways to improve the design or even a better way to structure it then let me know in the comments.
Tomorrow I’ll go through building the DB in MSSQL 2008 and creating the LINQ to SQL classes.
Wednesday, April 28, 2010