4

I am actually working on a social website. Not big, aimed at a very small group of folks. And I am curious, to how I can best implement Privacy settings for my users in a MySQL driven database. For example, on websites, like, Facebook, Google+, and probably other social websites, you find privacy settings and users can set these for their objects (e.g. content they share).

My issue isn’t writing the code in the language I am doing it in, but my issues are actually structuring up a DB schema that allows me to manage users’ settings. I have never done something like this, and would appreciate any help; also it must be scalable to some extent. Thanks a lot.

This is a JSON output I found from some website, and it looks like each part: allow, deny, description, friends, and value are all columns. However, in MySQL, you cannot really store large amount of comma-separated values, there's simply no space in a single column to do so.

{
  "privacy": {
    "allow": "",
    "deny": "299716926803603",
    "description": "Contacts; Except: Restricted",
    "friends": "ALL_Contacts",
    "value": "CUSTOM"
  },
  "created_time": "2015-09-04T13:01:40+0000",
  "id": "100002958951118_777860722322552"
}

I did something like this, but it simply doesn't work.

enter image description here

Privacy table:

allow       string
deny        string
description string
friends     string
id          int
object_id   int
owner_id    int
value       string

Not sure about this Privacy Table sample.

I'd appreciate a good solution, that is applicable, and most of the users in this community will benefit from this post.

4
+50

There is a spectrum of options open to you, depending on how much normalisation you desire, how much denormalisation you're prepared to live with, the use cases you have currently and what future functionality you reasonably expect to support.

The most trivial implementation would be to treat your JSON as a blob and store it accordingly, in a VARCHAR column. The application takes care of constructing the JSON and parsing it. MySQL acts as a glorified key-value store. This will be easy to implement. It is likely to scale well. There will be no flexibility of usage, no built-in data integrity and ad hoc queries will be awful.

Next from this is using a column of type JSON. The DBMS has insight into the content of the JSON and can optimise with indexes, more compact storage and validation etc. It is not what a relational database is optimised for, however, so is unlikely to be your best bet.

I include these two for completeness. Neither of them use the relational nature of MySQL. If you were to go this route you would be better adopting one of the many fine open source NoSQL solutions available.

To use the relational nature of your chosen product you will need to normalise your data. The given examples and business rules are sparse so I'll infer and extrapolate somewhat, but I believe my model will cover the essential aspects.

Obviously there will be an entity type called "User", with some large integer type as the primary key. I'll call this UserID.

All the different things you want to control visibility of will likely have their own representation. I'd suggest you use the supertype/ subtype pattern. Amongst other benefits this will abstract the security from other concerns and allow addition of new types of interaction without changing this model. I'll call the root type for all these types of things a Posting and give it a surrogate key PostingID.

Your example image has five categories of visibility, one of which is Custom. I'd suggest an entity type to cover this list. Let's call it Visibility. There is a question of how the system is to reflect changing friendships. If the Posting is visible only to Friends, and a new friendship is created, should that new friend see the old posting or should it remain shown only to those who were friends at the point of posting? Similarly when unfriended does the miscreant loose rights to see items? I'm assuming the dynamic option is what is preferred.

It is tempting to form an intersection of these three to hold rights to specific postings. This would get very large very quickly, however, and would have substantial write activity reflecting changing friendships.

Better would be to hold the Visibility as an attribute in Posting (a foreign key column) and the corresponding UserFriend list for each User. It may be possible to combine the "Friends" and "Friends except Acquaintances" lists with a flag to distinguish the latter, depending on your specific rules. I'm assuming that friendship is defined as mutual so if A is a friend of B then B is by definition a friend of A. This will double the number of rows in UserFriend but will simplify the semantics.

A separate entity type will be reqired when Visibility has a value of "Custom". This will list the specific UserID values for that PostingID.

From this the tables will be

User
  UserID

UserFriend
  UserID    -- FK to UserID; the owner of this list
  FriendID  -- FK to UserID; the friend's UserID

Visibility
  VisibilityID

Posting
  PostingID
  PostedBy      -- FK to UserID
  VisibilityID

PostingUser
  PostingID
  VisibleTo    -- FK to UserID; the friend who can see this posting

To construct a list of a user's friends' recent activitis one would read the given user's UserFriend rows, join to Posting on the friends' UserIDs, where the posting visibility was "public", "friends" or "custom". For those that have a custom list, join to that on PostingID and the given UserID.

Assuming the symantics of "deny" are "all my friends except this one" I'd implement this by adding a flag to PostingUser to differentiate allow and deny.

You can take a step back along the spectrum of options by denormalising the above. For example, PostingUser could be stored as a comma list in Posting, as UserFriend could be in Friend. This would reduce the IO and, likely, the elapsed time. The trade-off is that more of the work has to be done in the application instead of the DBMS.

Although Facebook famously uses MySQL the above is assuredly not how they do this. They most certainly have deployed many man-years of custom coding, caching, sharding and other tricks to make this work at their scale.

As a final point I would mention solving these sorts of problems are exactly what graph databases are for. Dispense with MySQL and implement this in, say, Neo4J.

  • WOW, this was a HUGE response. I am currently limited to a MySQL DB only, I have no NoSQL or MongoDB etc. Maybe in the future, but for now no. I was talking with someone earlier today, about allow, and deny, and that person thinks that both allow and deny are actually two separate tables and using MySQL JOIN one can then create a similar JSON output as the one I found, is this true? @MichaelGreen – John Smith Sep 6 '15 at 12:46
  • 3
    Separating the "deny" list into its own table would be a valid solution, too. Arguably it is a more normalised solution. I assumed the number of denys would be small and putting all in one table clustered by PostingID would reduce IO fractionally. But remember that it is legal to join to a table multiple times in a query. In this case one ON clause would have "...and flag = allow" while the other "... and flag = deny". So the separation, while valid, is not required. – Michael Green Sep 7 '15 at 13:32
2

Your question isn't very detailed, anyway here's how you could implement what you want:

The simplest way to model a privacy setting schema is probably to add a visibility field to each relevant property of the user profile:

User
------------------
id (int, PK)
username (varchar, IDX unique)
fullname (varchar)
fullname_vis (int)
email (varchar)
email_vis (int)
dob (date)
dob_vis (int)
nationality (varchar)
nationality_vis (int)

The int values mapped to each *_vis field set the visibility of the field with respect to the different groups, e.g.:

  • 0 = private (only the owner can see it)
  • 1 = friends
  • 2 = friends of friends
  • 3 = public (all other logged-in users can see)
  • 4 = fully public (everybody can see, even visitors that haven't logged in to the site, search engines etc.)

Clearly you have to define first the groups to which the different privacy settings will apply.

You can then fine-grain these privacy settings by setting each of these fields (the Custom button in your picture), or set all fields in bulk to a specific value (the Public and Friends buttons).

  • Does this approach scale well? – John Smith Sep 1 '15 at 14:10
  • I can't say. This is a simplistic approach; on a complex social network where you would also need to set up privacy settings for posts, pictures etc. it might not be efficient. The advantage of it is that it increases the database size of only one int per field. – dr01 Sep 1 '15 at 14:14
  • @dr01, Sorry, but your solution doesn't work out for me. – John Smith Sep 4 '15 at 18:08
2

First to answer your stated question on how to model the JSON:

User Table

  • UserId - My guess is the current user's UserId is one half of the "id" in your json and the other number is an organization id
  • Other user details

PrivacySetting

  • UserId - FK to the content owner
  • Description - Assumed to be user friendly description of the privacy setting
  • Friends - I don't know what the point of this is....
  • Value - Meaningless without more context
  • CreatedTime - Time stamp
  • Id - PK to uniquely identify this setting, this probably isn't the "id" in your json

PrivacyVisability

  • UserId - FK to the user table to associate a user with a setting
  • PrivacySettingId - FK to the Privacy Setting Table
  • IsVisible - Boolean flag for the allow/deny

When you need to get this data out of the database you would do one of the following:

  1. Write a select statement that gets all the privacy settings data without the allow/deny user lists. Write a second select statement that gets the privacy visibility settings with along with the PrivacySettingId so you can match the visibility setting to the privacy setting. Put both select statements into the same stored procedure. Map the result sets from the stored procedure to the application layer objects that will be serialized into your sample json. Probably Better
  2. Write one select statement that gets all the privacy settings data, and concats the allowed/denied UserId's into a comma separated string. Put the select statement into the same stored procedure. Map the result set from the stored procedure to the application layer object that will be serialized into your sample json. Probably Worse

This should technically qualify for the bounty because it gives you a workable method to translate the given json into a database schema. Unfortunately I doubt it really solves helps you solve your problem for the following reasons among others:

  • It isn't clear how to apply the privacy settings business logic even though you can output the requested json.
  • You haven't given us enough context/information to help you arrive at an optimal solution for your needs.
  • This solution is going to encourage you to filter/join based on text strings which isn't ideal in most cases

The database schema to support privacy settings is dependent on your application's needs. Since you haven't explained what your application privacy needs are it is nearly impossible to give you an optimal solution. Furthermore, the json sample you've give is undoubtedly intended to be used for updating a setting not applying a setting. While the information needed for updating the setting is related to applying the setting they are different enough that important business logic is missing. For example on the surface it seems silly that an allow and a deny list appear in the json. Presumably everyone who isn't allowed should be denied... The fact that they are both there is probably an artifact of a system requirement to allow group access to a resource EXCEPT for some group members, and to support allowing access to certain individuals. This matches the needs for Google Docs in a corporate setting, but it might not be what you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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