Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.