Search code examples
sqldatabasedatabase-design

DB Design: how to indicate if a column data is public or private

I'm designing a database and I have a question about how to make private some user data.

I have a user table, with name, city, birthday, biography, etc. And the user can make some data private (other users can see that data).

First, I thought to add columns to the table to indicate if a column is private or not. For example:

User
-------

user_id  | name | city | cityIsPrivate | birthday | birthdayIsPrivate
---------+------+------+---------------+----------+------------------

Or, another approach is to add a varchar column to indicate which columns are private:

User
-------

user_id  | name | city | birthday | privateColumns
---------+------+------+----------+---------------

And this privateColumns will have this: "city:NO; birthday:YES".

The user table will only have three columns that can be private or public. I will only have to add three columns more to the table.

Any advice?

Solution

  • Do not move data into a separate table if you are going to have to join to it every time you query the main table.

    Boolean (or equivalent) columns to indicate privacy for every column on which a privacy setting can be applied:

    1. is very simple to add.
    2. takes up practically no space.
    3. is much quicker to query.
    4. shows that the privacy settings are an intrinsic part of the user data.
    5. removes unnecessary complexity from the schema.

    The facts that these relate to other columns and that they represent a single kind of logical object are just a distraction. Go for simplicity, performance, and logical correctness.