Best practice for a Table with private columns I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ideas.

Extra field for each field

My first idea was to just add a boolean to each fiend to mark if the previos column was public.

---------------------------------------------------------------------------------------
| user_id | user_data_1 | user_data_public_1 | user_data_2 | user_data_public_2 | ... |
|---------|-------------|--------------------|-------------|--------------------|-----|
| int     | string      | boolean            | int         | boolean            | ... |
---------------------------------------------------------------------------------------


In this case I have to use IF(user_data_public_1, user_data_1, NULL) on the SELECT query.

Related table to mark public fields

Another idea was to have the table with the fields and a related table with to mark the public fields:

---------------------------------------
| user_id | user_data_1 | user_data_2 |
|---------|-------------|-------------|
| int     | string      | string      |
---------------------------------------

---------------------------------
| user_id | filed_name | public |
|---------|------------|--------|
| int     | string     | string |
---------------------------------


In this case I'd need to do the JOIN and check if the field is public on the related table.

I don't like so much any of the solutions, so is there some elegant and hopefully efficient solution to solve this type of design.

Thanks in advance.
A slight variation on Rolandos answer.  Rather than a TEXT column, you could use an INT column as a mask value.  Using a bitwise OR to test the mask would give you way to determine if the field is shown.

select id, 
    case when 1 | mask = mask then col1 end col1,
    case when 2 | mask = mask then col2 end col2,
    case when 4 | mask = mask then col3 end col3,
    case when 8 | mask = mask then col4 end col4,
    ...
from table


So if you want to show columns 1 and 3 then the mask would be 5.
Editing the mask is just a matter of adding and subtracting the appropriate values.
For example if you want to make column 3 public for a user

update table
set mask = mask + 4
where id = 1 
    and 4 | mask &lt;&gt; mask -- Make sure it isn't already set


Edit By @RolandoMySQLDBA

Perhaps to apply IF function to your idea

SELECT
    IF(POWER(2,0) | mask = mask,fld1,'Private') fld1,
    IF(POWER(2,1) | mask = mask,fld2,'Private') fld2,
    IF(POWER(2,2) | mask = mask,fld3,'Private') fld3,
    IF(POWER(2,3) | mask = mask,fld4,'Private') fld4,
    ...
    IF(POWER(2,n) | mask = mask,fldn,'Private') fldn
FROM user_table;


Maybe, this might work too

SET @xp = -1;
SELECT
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld1,'Private') fld1,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld2,'Private') fld2,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld3,'Private') fld3,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld4,'Private') fld4,
    ...
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fldn,'Private') fldn
FROM user_table;


so you don't have to hardcode the powers of 2,

This would only work for n &lt;= 32. Don't know what would happen if there would be 33+ columns.