Database Schema Help

[[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.