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
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.
You say, "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"
I agree with the first part of your statement only. You only need one table. For the sake of a name, I'll call it ShowableItems. Fields would be ShowableItemId (PK) and Item. Some of these items would be email, gender, address, etc.
Then you need a many to many table that shows what items can be shown to whom. Your three fields would be, the id of the person who owns the item, the showable item id, and the id of the person who can see it.