How to implement this logic in database?

Question

Here is the situation: I have a "user" , which have many attributes. For example, "name", "email", "password", "phone".

There are some attributes that are open for public, for example, "name", "email". These information is open for evenbody who visit the site.

But some, only for trust body in the system, for example "phone". These information is open for the people that the user trust.... (Assume the user have a trust list that can accept other user to the trust list.)

And the private one "password". This information is only for the user only, other people can't get access to it.

User can change different security level based on their need, for example, the user want to change the "email" for only trusted body, they can do so. It is also allow the user change their "phone" to public.

I use three number to represent three level of right. The first one with 3, second is 2, and the private is 1. So, I design the database in this way:

User
id(PK) 
nameId(FK) 
emailId(FK) 
passwordId(FK) 
phoneId(FK)

Name:
id(PK)
name(String)
securityLevel(int)

Email:
id(PK)
email(String)
securityLevel(int)

Phone:
id(PK)
phone(int)
securityLevel(int)

Password:
id(PK)
password(String)
securityLevel(int) //It must be 1

The question is, I can do it but my database will have many table, is there any simple way to do it? Moreover, is there any books about these kind of database design is recommended? thank you.

Answer 1

Whether or not the private data is segregated into a separate table does not solve the issue of how to prevent unauthorized access. The MySQL 5.1 manual section 5.4.5 discusses request verification/privileges, but if your database is hidden behind a web application with no direct access to your tables, then standard web server security alone might be sufficient. You should probably mention the entire os/server/db/language bundle you're using (LAMP, SAMP, whatever) so someone can suggest the best security scheme for your configuration.

Answer 2

If I understand this correctly, you could simply put all this information in two tables (user and friends) because as far as I know, it is a lot more efficient to get larger chunks of data with few queries, than smaller chunks of data with many queries. You would have something like this:

Users:
id
name
name_perm // 1, 2 or 3
email
email_perm // 1, 2 or 3
phone
phone_perm // 1, 2 or 3
password // Doesn't need permissions, always 1

Friends:
user_id
friend_id

When a user visits another user's page, first you check the permission level for each field. If level 2 is found, you would then query the friends table and check if current user ID is a friend of the user whose page is being viewed. If found, user is trusted and level 2 security info can be displayed. As for level 1 security, it's really simple - only display this info if both IDs match.

Hope this helps.

Answer 3

You don't need different tables for this, because each relation is a 1-1 relation.

Should a user have, say, multiple e-mail adresses, then you indeed should put the email and securitylevels in different tables. But because in this case, each user has exactly 1 email, 1 name, 1 phone, 1 password - just one table with 1 row per user should do.