Hi Guys,
I’m going to be making a social database driven application and it will allow people to have a friends list for each person.
I’ve seen a database that someone is using and they’ve got a table for users and in that table it has a field for which areas of the website the user has access to (a comma seperated list of areas).
Personally I don’t think this is the best way, as if I want to remove someone’s access to an area in sql, it’s very difficult (As they’re unique numeric keys).
I know that an UI could be made for this, but I’d ideally like it to be easy in sql (If I want to adjust thousands of records at a time) as well as a UI for specific people.
Is there a better way in terms of database design to get this done, or is this just the usual method.
I thought that the best method would be for 1 field to have 1 item in it.
Yes it did. What I always wonder is whether it would be best to create the friendship once and create a UNION query for both directions (and have an index on both, i.e. 2 indices, one for each direction), or create the friendship twice (one for both directions) and query directly (and have an index on the first friend’s id).
Off Topic:
I’ve had problems with MySQL tables crashing regularly in the past when all fields had a fixed data type (like INT). Setting the row format for the table to “fixed” solved the problem then. Just thought I’d give you a heads up.
disk space is cheap, a row consisting of two integers is fairly short, and you can fit quite a few into a couple of gigabytes
this depends on what the relationship means
if the relationship is “has a crush on” then you can see that if john has a crush on mary, this does not imply that mary has a crush on john, so there could be one or two rows
but if the relationship is “is friends with” then you would always have two rows and some people see this as an opportunity to simplify by storing only one row, but the downside of this is that in order to find a person’s friends you have to look for that person in either of the two columns
In the example I gave, the “area” was pretty similar to a website directory.
Each area have a unique numerical key, and each user has an “allowed areas” field with a comma separated list of the keys of the areas they have access to.
For my application though, it would be a user with a “friends” field, with a comma separated list of the userids of people he’s friends with.
My feelings are that this would be a bad implementation.
It’s not a hypothetical, it’s a database that I’ve seen before, I just thought it would be a good example of something similar that I wanted to do.
I was hoping you’d tell me that my current idea was wrong (As I knew it wasn’t the best way, just didn’t know the alternative.
I like your idea of having a friends table with 2 columns.
So if 1 person had 1000 friends, that persons id would appear 1000 times in the database, each time having the id of the the person he’s friends with, right?
But how could you reciprocate that without duplicating data?
For example, if person A has 10 friends, he will have 10 records, 1 for each friend.
But what would be the solution for each of his 10 friends?
Would they have a record of their own, with him listed as their friend?
So rather than just having 10 rows, I’ll have 100.
This quickly scales up if 100 people are all friends with each other.
Would you suggestion still apply or are there alternatives?
I guess you could do a piece of code to first say something like:
If person A is friends with person B, then person B cannot be made friends with person A.