使用Json格式减少MySQL中的JOIN

Question

I have many tables in my database, an example is the table fs_user, the following is an extract of the table columns (dealing with privacy settings):

4 Columns from the table fs_user:

show_email_to
show_address_to
show_gender_to
show_interested_in_to

Like many social networks, I need not only to specify which data is private and which is public, but also which data is available to a chosen users, and which one is not.

As I have about 30 data like the 4 data above, I think it will be bad to create one table for every data, and make a many to many relation with the table fs_user.

This is why, I got the idea of saving this data in a Json form for every column (whose type=TEXT), example

show_email_to => {1:'ALL',2:'BUT',3:'3'}

This data means, show email to all users, except the user whose id=3.

Another example:

show_email_to => {1:'NONE',2:'BUT',3:'3',4:'80',5:'10'}

This means, no user will see the email except the users id=3,id=80 and id=10.

Of course, the MySql query will select this data, and PHP/Js will extract the data I need from Json.

Another point, is that sometimes .. a user wants to show data only to his friends except 3 friends.

This will do :

show_email_to => {1:'FRIENDS',2:'BUT',3:'3'}

This means that the email will be shown to all his friends, except user with id=3.

My question is : How much will be this system performant, flexible (for other uses) compared to the 'many to many' solution (which requires to have many data in many tables)??

Note: I know already that saving many elements in one column is a bad practice, But here: I think this is a json element and can be considered as a one Object

Answer 1

This is a good question. What you propose is, with respect, a very bad idea indeed if you're using any flavor of SQL. You are proposing to denormalize your tables in a way that will defeat every attempt to speed up searching or querying in the future.

What should you do instead? You could take a look at using an XML-centric dbms like MarkLogic. It's capable of creating indexes that accelerate various Xpath-style queries, so you would be able to search on relationships. If you do that, I hope you have a big budget.

Or, you could use normalized permission tables.

   item_to_show  (item id)
   order (an integer specifying rule ordering,  needed for this)
   recipient (user id)
   isdenied  (0 means recipient is allowed, 1 means she is denied)

In this table, the primary key is a compound key constructed of the first two columns.

I'm aware that you have many types of items. You assert that it's bad to have an extra table for each item type in your system. I don't agree that it's inherently bad. I believe your proposed solution is far worse.

You could arrange to give each item a unique id number to allow you to use a single permission table. See this for an example of how to do that. Fastest way to generate 11,000,000 unique ids

Or you could have a single permission table with a type id.

   item_to_show  (item id)
   item_type_to_show (item type id)
   order (an integer specifying rule ordering,  needed for this)
   recipient (user id)
   isdenied  (0 means recipient is allowed, 1 means she is denied)

In this case the primary key is the first three columns.

Or, you can do what you don't want to do and have a separate permission table for each item type.