How can the following JSON output be modeled in a database?

Databases
1

On websites, like Facebook and Google+, you find settings (a.k.a. Privacy settings) which allow you to decide who is allowed to see what you share. How are such settings structured, or what is the Database schema for achieving such functionality.

{
  "privacy": {
    "allow": "",
    "deny": "",
    "description": "Your Contacts",
    "friends": "",
    "value": "ALL_CONTACTS"
  },
  "id": "100002958951118_777860722322552"
}

Restrict someone:

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

What database are you using? Many are starting to come with JSON datatypes, so you would just store the id in one column and the privacy in another.

https://dev.mysql.com/doc/refman/5.7/en/json.html
https://mariadb.com/kb/en/mariadb/dynamic-columns/#column_json

MS SQL will have it in 2016
http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx

3

I am using MySQL. But that’s irrelevant for now. I am curious, how this output can be modeled in a Database, think the DB schema design.

See this URL:
[http://i.stack.imgur.com/NRACQ.png]

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.
[http://i.stack.imgur.com/NRACQ.png]

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

5

http://i.stack.imgur.com/NRACQ.png

The ID not an integer, it’s a string, so it should be stored as a varchar.

You can store up to 65,535 in a single varchar column. There are larger columns if you need it. The JSON field I mentioned earlier is one of them.

However, the “friends” field you’re looking at is probably not individual friends, but predefined groups of friends. The groups are probably stored in another table. This would make the actual list of friends very smell. Or you could have a one to many relation table sitting in between, which is the preferred method.

How well do you understand normalization?

So, basically you would have your user table which looks like:

USERS
user_id | name | some other columns

Then your privacy table. Leaving out the “friends” column. The user id here is who owns this set of privacy settings.

PRIVACY_SETTINGS
privacy_id | user_id | allow | deny | description | value

Then you would your groups.

FRIEND_GROUP
group_id | user_id (owner) | group_name | group_description

Then a junction table. This is the table that maps the groups, to the users in that group. The group table stores who owns the group.

GROUP_MEMBERS
group_id | user_id

So to get this data out, you would use JOINS and multiple queries.

SELECT * FROM users u -- grab the users first
LEFT JOIN privacy_settings ps ON ps.user_id = u.user_id -- LEFT JOIN the privacy table
WHERE u.user_id = ?

Then it would be easier to do other queries to get the groups out and populate those into comma delimited fields with SQL. You probably don’t need who’s actually in the group every time you pull the group.

You can see that normalizing it gets very complicated very fast. That’s why the JSON datatypes I mentioned earlier are great. You can just take your entire privacy object and store it in a single queryable blob-like field. This method is very similiar to NoSQL approaches, like MongoDB or CouchDB.

1 Like
6

Interesting. However, aren’t Allow and Deny two separate tables as well? Also, Description and Value represent the Privacy setting e.g. a Group.

If I’d put Deny and Allow as two separate tables how would this look? Also is it a good practice to do so?
Can you possibly show me how this looks live? An http://sqlfiddle.com/ would do the job just fine, also the community would benefit from this. Thanks a lot.

7

I don’t see why they would be their own tables, but I don’t know how the data is being used or even why they are large numbers.

If you’re building this yourself, you can store it any way you want. There isn’t any reason to copy what someone else has done for some purpose you don’t have.

8

My server is currently limited, with mysql 5.0, not even 5.6 or 5.7. So therefore, I have to implement things in an approach that is as it is now, mainly because of mysql being 5.0 for now. Do you get what I mean?

So how would this be, if Allow and Deny were two separate tables?

9

I can’t think off hand why they would be 2 different tables in this scenario.

10

I was told the following by a user on StackExchange,

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.

11

That makes sense.

Remember while normalization is good, it does add a lot of complexity so doing it where it isn’t needed isn’t always a good thing.

12

I am aware that normalization isn’t good from a performance perspective. And yes, it can indeed become very very complex.

If I decide to go with three tables, “Deny”, “Allow” and the other one, how would this be in a Database Schema?

13

Well, I don’t know what you want to store there. It would just be a basic FK relation. Same thing I described above for the groups, but you don’t need a junction table.

ALLOW
------------
allow_id | other_stuff


DENY
-----------
deny_id | other_stuff
14

WOW man, you are insane dude! Thanks a lot, I will try this. Yeahhh! :smiley:

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.