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