Page 2 of 3

Re: friendlist database structure...any suggestions appreciated

Posted: Sun Sep 13, 2009 7:08 pm
by Eran
I'd stay with the one-table plus status field structure. This is essentially one relationship (connecting two user IDs together), so putting it in separate tables would be denormalizing for no apparent gain. You can separate the status into two fields if you feel it is too confusing conveying all statuses using one (as such who issued the friend request, etc.)

Re: friendlist database structure...any suggestions appreciated

Posted: Sun Sep 13, 2009 7:22 pm
by scarface222
Do you mean like headings: user_1, status, user_2, status

Re: friendlist database structure...any suggestions appreciated

Posted: Sun Sep 13, 2009 7:29 pm
by Eran
something like that, only differentiate the statuses so you'd know which belongs to which user

Re: friendlist database structure...any suggestions appreciated

Posted: Sun Sep 13, 2009 8:13 pm
by scarface222
Yeah so like status 1, status 2. I think that can work but it seems a little more complicated. I am not really sure how I would word my mysql statements to say select who has a request. For example status 0=none 1=request 2=friend 3=block. Say an entry looks like this user1: 1, status1: 1, user2: 2, status2: 0. I am not sure how to approach this. It would take up less space I suppose and be more efficient, but it seems easier with 3 tables to work with the data like superdezign said. What foreseeable benefit could 1 table have in the long run?

Re: friendlist database structure...any suggestions appreciated

Posted: Tue Sep 15, 2009 7:29 am
by superdezign
pytrin wrote:This is essentially one relationship (connecting two user IDs together), so putting it in separate tables would be denormalizing for no apparent gain.
I disagree. What is this "one" relationship? Being friends and requesting to be friends are not the same thing. Using a status would be saying that while a friend request is active, the users are friends. This is incorrect logic, and doing so only serves to save space, which is something that we have more than plenty of.
scarface222 wrote:but it seems easier with 3 tables to work with the data like superdezign said.
As for user blocking, I would agree that a third table is in order.
scarface222 wrote:What foreseeable benefit could 1 table have in the long run?
If you consider slower queries a benefit, then that. With three tables, you only query the necessary table with simple queries rather than querying a single table every time with complex queries. You'd only check the friend requests table when friend requests are being shown or dealt with. You'd only query the blocked users table when you need to know which users are blocked, which is likely not going to be often. You'd only query the friendships table when you need to know a user's friends, and you wouldn't have to make unnecessary joins of the same table with itself.

I stand behind my logic.

Re: friendlist database structure...any suggestions appreciated

Posted: Tue Sep 15, 2009 7:40 am
by Eran
I disagree. What is this "one" relationship?
The relationship is users to users. Once a user initiated a request he established a relationship with another user. The type of that relationship is indicated via the status attribute. You can disagree with the approach but I don't see how you can refute the logic itself.

In the same manner you could store users with different statuses (confirmed, banned etc.) in separate tables and call them users, unconfirmed_users, banned_users and say they all represent different entities. While it will work, that is not a normalized schema. Your approach calls for adding a table with each new status, which is not scalable or maintainable.
If you consider slower queries a benefit, then that. With three tables, you only query the necessary table with simple queries
You denormalize for speed after you encounter performance issues, not start with a denormalized schema. Properly indexed, such a simple table should have no problem scaling to millions of records. That, and there are type of queries that would require joins or UNION between the tables to get all the data, which in theory would make it slower, such as showing all your connections - pending, current and blocked (as some networks do).

Re: friendlist database structure...any suggestions appreciated

Posted: Tue Sep 15, 2009 5:09 pm
by VladSun
I vote for "the one-table plus status field structure" too.
Also, I think there is no need for separate "friendship" records for both users. The primary key of the "friendship" table should be a composite natural key - user1_id and user2_id.

Re: friendlist database structure...any suggestions appreciated

Posted: Tue Sep 15, 2009 8:54 pm
by scarface222
You will have to excuse my inexperience, but what is a composite natural key, will I need to use joins if I use one table, does having one table vs. 3 provide a difference that users will notice (what if my site becomes quite large, will the table structure greatly effect query time?), and can someone please explain in detail a one table structure that I can understand? Currently the 3 table structure seems most logical to me simply because it is the easiest to understand and seems easiest to query data from when working with the database. Also, it is unclear to me the amount of benefit one table will provide over the 3 table option. If it is milliseconds that a user will not notice, obviously this is not an issue. As an example for the one table structure, user1_id, user2_id, status1, status2 was the suggested table design. How will this table function and what type of queries will I have to make? Thanks again everyone.

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 3:20 am
by VladSun
By using "composite natural key" I mean that you should not use an additional column ID (now, that is a surrogate key) for primary key. The table structure should look like this:
[sql]CREATE TABLE friendship ( user_id int, related_user_id int, STATUS enum('pending', 'approved', 'blocked'),PRIMARY KEY(user_id, related_user_id));[/sql]

Some SQL queries:

-to find user friends with status = approved:

[sql]SELECT     * FROM     userINNER JOIN     friendship ON         friendship.related_user_id = user.id        AND         friendship.user_id = $USER_ID        AND         friendship.STATUS = 'approved'[/sql]

You see - we have "friendship.related_user_id = user.id and friendship.user_id = $USER_ID". That's why we a need composite key.

If you ever need another "status" for friendship you will just have to use another status value, not a whole table.

I think, pytrin has explained the advantages of using a single table and disadvantages of using separate status tables. Read'em :)

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 7:30 am
by superdezign
pytrin wrote:That, and there are type of queries that would require joins or UNION between the tables to get all the data, which in theory would make it slower, such as showing all your connections - pending, current and blocked (as some networks do).
I agree with that.

But using one table, while it may be more proper (as you and Vlad have proven), adds an extra layer of complexity to the problem. Firstly, every query will include table joins. This is similar to algorithm design, in that if two algorithms have the same worst case run-times, the algorithm with lowest average run-time is preferred. Also, after every query, one would have to then deal with the data in PHP by checking which of the user_id columns is the current user and which is not.

I understand that the relationship is a user-to-user relationship in strict database terms. But, in practical terms, the relationship could be viewed as friend-to-friend or requester-to-requestee.
VladSun wrote:-to find user friends with status = approved:

[sql]SELECT     * FROM     userINNER JOIN     friendship ON         friendship.related_user_id = user.id        AND         friendship.user_id = $USER_ID        AND         friendship.STATUS = 'approved'[/sql]
This query does not work when the target user is the related_user_id. You'd need to include the other direction of the join as well.

Code: Select all

SELECT * FROM user INNER JOIN friendship ON
friendship.user_id = user.id AND friendship.related_user_id = $USER_ID AND friendship.STATUS = 'approved'
OR
friendship.related_user_id = user.id AND friendship.user_is = $USER_ID AND friendship.STATUS = 'approved'

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 7:48 am
by VladSun
superdezign wrote:This query does not work when the target user is the related_user_id. You'd need to include the other direction of the join as well.
VladSun wrote:Also, I think there is no need for separate "friendship" records for both users.
I think that even user A has registered user B as "friend" it doesn't mean that user B feels like user A must be in his friend list. I think it's more business logic, rather than software design ... :)

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 7:54 am
by VladSun
superdezign wrote:Firstly, every query will include table joins.
Your approach would require JOINs too.

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 8:03 am
by superdezign
VladSun wrote:Your approach would require JOINs too.
It might, but not necessarily must. Facebook and Myspace separate your friends list and your friend requests, so there would be no reason to join tables in order to retrieve both of these at the same time.
VladSun wrote:I think that even user A has registered user B as "friend" it doesn't mean that user B feels like user A must be in his friend list. I think it's more business logic, rather than software design ... :)
Ah, that is similar my suggestion of each mutual friendship taking two entries. I completely agree with you on that, in the interest of saving query complexity. But your logic makes it more practical in terms of real-life logic rather than just simplifying the developer's job. Your logic is now mine. :twisted:

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 8:15 am
by VladSun
superdezign wrote:
VladSun wrote:Your approach would require JOINs too.
It might, but not necessarily must. Facebook and Myspace separate your friends list and your friend requests, so there would be no reason to join tables in order to retrieve both of these at the same time.
I think a JOIN will always used ... maybe you should give us an example?
superdezign wrote:Ah, that is similar my suggestion of each mutual friendship taking two entries.
It's similar, but only if user B requests frinedship too. :)
superdezign wrote:But your logic makes it more practical in terms of real-life logic rather than just simplifying the developer's job. Your logic is now mine. :twisted:
:P Usually software design follows real-life logic, rather that developer-life logic :twisted: :twisted: :twisted:

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 8:21 am
by VladSun
VladSun wrote:Usually software design follows real-life logic, rather that developer-life logic :twisted: :twisted: :twisted:
Once upon a time ... a software developer was told by his wife:
- Go to the supermarket and take a piece of sausage. And if they have eggs take 10.

... developer is back from shopping with 10 pieces of sausage ...

Guess what - there were eggs in the supermarket.
;)