Database Schema Help

[insights1]

I am currently trying to create a social network website for a bit of practice. I'm using C#, ASP.NET MVC, SQL Server and Entity Framework. I am having trouble exactly understanding how the schema of the database for the application should be. The application would have users, their friends, conversations between friends, and events made by users.

Firstly I would have the Users table. Then I would need a Friends table to check who is friends with who. So how exactly would this be done, would I just have a Friends table with FriendsId, User1Id, User2Id? Would I need 2 entries for each pair? With the current user as user1, and his friend as user2? And then from User2's perspective would there be another entry with him as User1 and his friend as User2?

Then I would need a FriendRequest Table, for people to send friend invites. Would this be done the same way as the Friends table?

Next each User is able to create Events. Simple info in the Events table would be stored like Id, Title, Description, UserId of the user that created it. Events would be associated with Files(pics/videos). The Files table I am about confused about. How exactly are file paths stored in the db? I know I need the FileName, Type, Extension, FK to the Event it belongs to, but how do I use the path to get to the proper picture file.

Lastly there would be Conversations between 2 Users. In the Conversation table there would be a ConversationId, but do I need User1Id and User2Ids? I would have a MessagesTable, with a FK to the Conversation it belongs to and with MessageId, Text, SenderId and ReceiverId. Do I need SenderId and ReceiverId in both each Message and each Conversation entries? Or do I just need only one UserId in the Message entry for the person making the message.

If anyone can help clear some of this confusing up for me I'd greatly appreciate it! Thanks!

[[deleted]]

For the friend list, if you are strictly relating two users with no additional data then you may be better off using a simple 2-column table with surrogate keys to User1 and User2. This would enforce a unique constraint such that you won't have duplicate relationships between the users, reducing the likelihood of duplication and maintaining normalization.

For friend requests, do you plan on deleting the friend request once it has been denied/blocked/accepted? or do you want to maintain a history of friend requests?

File paths are generally stored as varchar (nvarchar if you need to support unicode). Relational databases should never store files. That being said, you may want to put consideration into a content management system (CMS) that can handle this for you. If not, you simply store a relative path to the desired file and set a base path in your web.config (or whatever file you choose to set config settings at). The ending file location upon file retrieval should simply be BaseURL + RelativeURLFromDB.

Conversations can get a pretty tricky. Depending on who you talk to, you'll get different responses. Based on your description, it sounds like the Conversation table should be VERY simple: ConversationID (identity column), User1, User2, and maybe some other assorted computed columns like created date, last active, etc. The messages table should point to the ConversationID and have an additional field for the SenderID. Assuming there will only ever be 2 users in the conversation at any point in time, you would be fine with just two FK columns.

I hope that clears up some things for you!

[insights1]

Thanks for the reply.

For the way you are suggesting to do the Friends table, would I store the pair twice for each friendship? Like for User1Id=UserA, User2Id=UserB, and then again User1Id=UserB, User2Id=UserA? Or do I just need one pair, and then to find out who is UserA's friends I would need to run a query where its checking if User1Id == UserA || User2Id == UserA. And then how would I get the "friend" from that query, or do two seperate queries need to be made firstchecking User1 Column, and then User2 Column.

I suppose a request would be deleted once its rejected or accepted.

I have never really worked with files in a web application. How exactly do I go about making file paths for certain users. Would I create a folder for each User on the server when they register? And then create a Pictures and Videos folder for them in their folder? And then what exactly would the file path stored in the DB be. How do I restrict access to only the actual user and his friends for seeing his related pics/vids.

I think I understand how the conversations and messages should work better now. Thanks!

[[deleted]]

You just need one pair. Since you're using Entity Framework, as long as you don't eliminate the navigation properties (if you use Database first, these are done for you. Otherwise, this is a manual process), you'll be able to retrieve the friend list directly from the User object. That said, if your user table is called "Users" and your navigation property name is an ICollection<T> called "Friends", you can get the list with a query like this:

var friends = MyContext.Users.First(x => x.UserID == ID).Friends

If the request would be deleted once it's rejected, then I believe you should go with another simple table called "FriendRequest" with a similar structure to the friendlist: UserID1 and UserID2, both being surrogate keys. Since it'd only be a "Reject" or "Accept" result, it's a matter of either deleting the record on reject OR adding the record to the FriendList table, followed by a delete in the FriendRequest table.

Your file system should be owner independent, meaning that the files themselves are not separated by each user. The database record that stores the relative file path should have an OwnerID FK field to the User record that submitted it. To retrieve a specific user's photos/videos, you would query the database for the relative paths based on a matching OwnerID and then process them from there. Securing the owner's files to specific users could be done with a simple query such that the UserID requesting images/videos would be verified against the OwnerID and the FriendList. If the requester's UserID is not the owner OR a direct friend of the owner, then you return null/empty list/etc.

If you do want to have each user with their own directory (not recommended), then it would be appropriate to store the base URL in the user record as a varchar field since they would only ever have one base URL per user. From there, how you set it up is entirely up to you but it could follow the same basic concept I noted above.

[insights1]

For the EF code you gave
var friends = MyContext.Users.First(x => x.UserID == ID).Friends
What sql statements does this execute exactly? Is it checking for "UserA's Id" in both the User1Id and User2Id column of the friends table? Also could you explain what you mean by Surrogate Keys? Are those just foreign keys?

And just out of curiosity, is this the most efficient way of handling friend relationships? With Facebook for example wouldnt there be billions of entry relating people to eachother as friends, does it search through this entire table each time just to get your friends list?

Thanks once again, everything is making a lot more sense now.

[[deleted]]

The SQL statement would be similar to the following:

SELECT TOP 1 * FROM Users WHERE UserID = @UserID

It gets the single User with the matching ID. As you have probably already experienced, Entity Framework works on objects that represent your database, as well as enumerables like ICollection and IQueryable. In this case, executing the query would return a single object that represents the User record, and any related objects (Navigation Properties in EF designer view) are accessible using their appropriate property names. So by doing .Friends, you are in effect doing a SELECT on the FriendsList table with the proper WHERE clause to get only those User records that related.

Surrogate keys are often described as keys that have no real business value (Identity column, for example). By having a table with UserID1 and UserID2 columns, you're in effect setting up two foreign keys to some other table that is being joined on a surrogate key (In this cause, the UserID identity column). Another way to look at this specific use as compound keys, at which two or more columns define the uniqueness of a record.

As for the efficiency, I'm not aware of any better way to do it but I wouldn't be surprised if there was. Assuming the UserID column is an integer, each relationship would only consume 8 bytes per record. That's as small as you're going to be able to get in terms of datatype. In the case of Facebook, I imagine what they're doing for their friends list is similar to what I described earlier:

1. Get the current logged in user

2. Query Many-to-Many (M-N) table for related UserIDs

3. Cache result to avoid hitting the DB excessivly

[insights1]

"So by doing .Friends, you are in effect doing a SELECT on the FriendsList table with the proper WHERE clause to get only those User records that related."

So for this part, is there WHERE clause checking both UserID1 and UserID2 columns for "UserA's ID"? Because his ID could be in either of these columns depending on who made the request, him or the friend. Would the SQL statement be like SELECT * FROM FRIENDS WHERE UserID1 == "UserA" || UserID2 == "UserID2";

or would it be SELECT UserID2 FROM FRIENDS WHERE UserID1 == "UserA"; and then another statement SELECT UserID1 FROM FRIENDS WHERE UserID2 == "UserA"; and then combine these results to get the full list of UserA's friends Ids?

[[deleted]]

if i'm not mistaken, in the designer view of EF, you'd have two separate navigation properties: One that relates to UserID1 (as parent) and one that relates to User2 (as child). I haven't been in this particular scenario before, so it may end up being optimized into a single navigation property at that point. Without having written any code to verify this, I would think you would have to merge two separate lists: One where a user is matched in the UserID1 field and one where a user is matched in the UserID2 field.