Human relations between users

Question

I'm involve in a development of a tiny Social Network where users must be able to establish relations between them and also have permissions over contents. For a example: I add one user as my friend but this user doesn allow me to see all his/him contents so I only have access to those contents that user allow me (permissions). So I have a problem and need help designing the DER of this part. I think in have this tables:

- users (id, name)
- relations_type (id, name, active)
- users_relations (id, id_user_1, id_user_2)
- users_permissions (id, id_relation, id_module, id_user, view, edit, delete)

This cause the following:

• Two rows for every relationship: User 1 > User 2 and User 2 > User 1 because when I search (SELECT) I need to know which are User 1 friends and also which are User2 friends. If I leave only one way relationship then I need a UNION and this migth slow my DB

Is that correct? How yours handle this when CRUD on this? I'm using MySQL by the way and MyISAM tables.

Cheers and thanks in advance

Answer 1

You may want to recognize directional relationships (e.g. "is the mother of", "is the son of") or you may need to acknowledge that relationships are not bilateral insofar as the users don't grant each other exactly the same rights. In that case you need to keep two records in users_relations for each relationship. Otherwise, I would just capture each user linkage once.

Union queries take about twice as long as single queries, but when these queries are built on fast operations like index seeks this isn't so bad. In any case you'd be trading this off against twice as much storage, which mitigates the impact slightly.

I'd be much more concerned about the potential headaches of managing the redundant data (if it is indeed redundant).

You could pick a convention like lowest id_user first to make certain kinds of operations more predictable - such as updates and deletes.