Database Design - Users and Their Privacy

Question

Basically, I have a table for my users (this is a nightclub), and now I'm trying to improve their privacy.

So far, I'm just showing some login information, but now I want to let users choose this.

My question is: what would be the best way to do this?

What do you recommend to me, create a new property for each property that I want users to manage privacy?

Example user table:

-Id

-Mail

-Telephone

-ShowEmail (int), and 0 to anyone, 1 to login and 2 to friends

-ShowPhone (int)

It's a good choice? I'm not quite sure if I should create a new table for setting privacy. I must admit that database design is not my specialty, so in fact we need some feedback.

Thanks!

Edit: privacy is not just the simplest properties. I will need to handle events that the user has registered, photos, where the user is tagged, etc.

Answer 1

This is an opportunity, but it makes your table very large and with extra redundant fields. You have two options that I personally like better:

• Use the flag field. The advantage is that you only need one field, the disadvantage that it distracts from the good relational database methods and that queries become more vague if you want to select something from it.
• use another table with three fields, for example. "UserID", "FieldName" and "ShowTo", the last of which is ENUM (or integer if you like it). This works more, but is clear and imho is much better.

Requests will look something like this: 1.

SELECT phone, (privacyFlags&8) AS showPhone FROM users 

2.

 SELECT user.phone, privacy.ShowTo AS showPhone FROM users JOIN privacy ON privacy.userID=users.userID WHERE privacy.FieldName = 'phone'