1

I am designing a database for MySQL to create a relationship between two users of an application. I am unsure of the best way to store the particulars of this relationship, though I currently intend to use one record per relationship in a link table called RELATIONS. Each user has basic data like their name and occupation stored in the USERS table, and more specific personal data stored linked by FKs in other tables, which we'll say are called OTHER1, OTHER2, and OTHER3, which will all contain some other data to be shared, we'll say each in a field called [Data] and identified with ID and USER_ID.

The difficulty is that the application allows users to specify what basic and advanced data that they show to each user, and vice versa. The RELATIONS link table needs to have FKs to USERS for the two users to set up the relationship, but I don't know how best to specify what data each user is able to share, because virtually all of the data that the database stores is stored optionally but all needs to possibly be hidden from a user that doesn't have permission to view it. The second user should be able to see if there is data there, however, so that he might request permission to view it.

My model for RELATIONS at this point looks like this:

RELATIONS

ID
USER_ID1
USER_ID2
USER1OTHER1_ID [(Value), Unshared, Null]
...
USER1OTHER100_ID [(Value), Unshared, Null]
USER2OTHER1_ID [(Value), Unshared, Null]
...
USER2OTHER100_ID [(Value), Unshared, Null]

So USER1OTHER1_ID will contain the FK to OTHER1 if User1 has shared it with User2, will be "Unshared" if it's present but unshared, and Null if User1 has no data in OTHER1. Same for USER2OTHER1 for sharing with User1. I don't like having a massive field array, though, and I don't like how I'd have to update all the relations if User1 later decides to add data to OTHER1. Is there a simpler and more normalised way of representing this?

Randy
  • 16,480
  • 1
  • 37
  • 55
Callum Jenkins
  • 103
  • 9
  • i guess you are aware - and not that this affects you directly - but many security systems consider the simple knowledge that data exists a security risk. – Randy Jul 07 '12 at 18:43
  • Yes I have been considering removing the ability to know of unshared data and to request it partly for that reason, but it's currently part of the design. I might compromise and create a user preference to disallow requests, similar e.g. to how Facebook has the abilities to block incoming friend requests or be seen on directory searches. – Callum Jenkins Jul 08 '12 at 00:09

4 Answers4

3

I believe the normalized approach would be to only store whether userA has permissions to view the userB's data and not add FK references to it in the Relations table because you already have references to userB's data somewhere else. By storing additional references in the Relations table you are duplicating data and will have to ensure that it stays synchronized as you described in your question which will probably be an ongoing maintenance hassle and one more thing you have to keep in mind whenever you refactor your code.

If you only store permissions (no fks) in the Relations table you would join on a table (User?) to get the User's shared data or to see if it exists depending on the permission.

As far as having an excessive number of columns on the relations table, I don't think you will have enough to see a real degradation when querying the table (you could correct me on this). For clarity sake in the db code as well as your application code, I think you are better off to have a column for each permission than to try to find a short cut such as combining them in a clob or something.

Planky
  • 3,185
  • 3
  • 29
  • 39
0

The most succinct way I can readily imagine is to store one INT with the relationship, which is a bit-wise representation of the permissions; with the interpretation of that INT in the code. The INT will need as many bits as you have unique permissions, and then define constants for each of them. I'm not sure what language you are implementing in, but there about a few ways to skin this cat...

So, some pseudo-code might look like this:

define RELATION_PERMISSION_SEE_MY_PHOTOS = 1;
define RELATION_PERMISSION_SEE_MY_FRIENDS = 1<<1;
define RELATION_PERMISSION_SEE_MY_EMAIL = 1<<2;

and then build some arrays of supporting info (like localized strings, etc) to build your interface with, and then do something like this to modify it:

int new_permission = 0
foreach(user-selected-permissions as selected_permission) {
  new_permission |= selected_permission
}
my_relation_model.permissions_flags = new_permission
Chris Trahey
  • 18,202
  • 1
  • 42
  • 55
0

one way would be to use essentially key value pairs..

similar to this:

user_1_id
user_2_id
field
privilege
Randy
  • 16,480
  • 1
  • 37
  • 55
  • So you mean to create one narrow table, where each record describes one permission, and thus it takes multiple records to describe the precise nature of the relationship? I had initially been inclined to avoid a multiple record solution, but this would certainly avoid all the Null values that would be present in my single record solution. – Callum Jenkins Jul 08 '12 at 00:12
0

.. because virtually all of the data that the database stores is stored optionally ...

Considering this, I would suggest 6NF for all user attributes.

  • The User table serves as an anchor and ideally holds only UserID.

  • Each user attribute has its own table with only UserID and the attribute value (6NF); a row exists only if the attribute is specified (all attribute values are NOT NULL).

  • Each attribute has also a sharing table with only OwnerID, VisitorID. A row exists only if the owner shares the attribute with the visitor.

enter image description here

  • In this model, a user can share only attributes that do exists. If you want to allow sharing of not specified attributes, point the OwnerID to the User table too.

To make thing simpler you can (should) create a view(s) for user data.

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • I had considered this extreme of normalisation as a solution - and it is elegant - but I would worry for performance and ease of development when I need to perform many joins just to display even a small amount of data for one user. – Callum Jenkins Jul 07 '12 at 23:50
  • @CallumJenkins; This type of data-structure is accessed through a layer of views and stored procedures. So, the application layer sees only these. MySQL does not allow for join-elimination, but MariaDB does. Also triggers on views are OK in MariaDB (I think?). Once you have join-elimination, all is good. – Damir Sudarevic Jul 08 '12 at 00:23
  • @CallumJenkins; Take a look at this http://stackoverflow.com/questions/10456790/what-are-the-pros-and-cons-of-anchor-modeling/10471822#10471822 and follow the link in the answer. – Damir Sudarevic Jul 08 '12 at 00:25