Best practice for a Table with private columns

Question

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.

Answer 1

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 <> 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 <= 32. Don't know what would happen if there would be 33+ columns.

Answer 2

I'll go with a mix of some of these ideas: A related table with a INT mask field for permissions as MickyT suggested.

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

-------------------------------
| user_id | public_perms_mask |
|---------|-------------------|
| int     | int               |
-------------------------------

This method even will give you the choice to expand your application and make it user-to-user specific permissions. Something like:

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

-----------------------------------------
| user_id | target_user_id | perms_mask |
|---------|-----------------------------|
| int     | int            | int        |
-----------------------------------------

Answer 3

I have another solution that combines the two ideas

You will not need to do joins as my suggestion will not involve another table.

You will need extra fields to monitor public columns. Actually, you only need one field.

---------------------------------------------------------------------------------
| user_id | public_flds   | fld1   | fld2 | fld3 | fld4   | fld5 | ... | fld20  |
---------------------------------------------------------------------------------
| int     | fld1,fld2,... | string | int  | int  | string | int  | ... | string |
---------------------------------------------------------------------------------

The TEXT column public_flds will contains a comma separated list of field names

You could do something like this:

Say the user_id was 27. Look up the public_fields based on id 27

Take that list and use it in the SELECT clause

SET @_id = 27;
SELECT public_flds INTO @flds FROM user_table WHERE id = @_id;
SET @sql = CONCAT('SELECT ',@flds,' FROM user_table WHERE id = ',@_id);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

This would only apply if you are choosing to query one id.

If you are displaying multiple users, it gets a little hairy. You would have to sculpt the query for each column by searching for the field name in the list of public fields, displaying the field if it is found, 'Private' otherwise.

SELECT
    IF(LOCATE(CONCAT(',',fld1 ,','),CONCAT(',',public_flds,','))=0,'Private',fld1 ) fld1,
    IF(LOCATE(CONCAT(',',fld2 ,','),CONCAT(',',public_flds,','))=0,'Private',fld2 ) fld2,
    ...
    IF(LOCATE(CONCAT(',',fld20,','),CONCAT(',',public_flds,','))=0,'Private',fld20) fld20
FROM user_table;

The only cost is the setup the UI to do the following:

• Adding columns to public_flds when making a column public for a specific user
• Removing columns from public_flds when making a column private for a specific user

Once you do that, the query become more data-driven.

Answer 4

I'd go with a separate lookup table holding the user_id and the column_name's being public.

Whenever a user marks a column as being public, it'll be inserted in that table. If marked as private, it'll be deleted.

Then I'd create a itvf function with the user_id as input parameter. Within the function I'd unpivot the result of the Select against the user_data, perform a left join against the table holding the information what columns can be displayed and Pivot the data. The value of any column that's not in the lookup table would be replaced by either blank or PRIVATE or whatever. The app finally will decide what to display in case of values indicating a private column.

This will allow to have your main table narrow (speeding up the SELECT) as well as the lookup_table being narrow, too (since there are only the rows included that need to be displayed).

With this concept you could even use a set based solution since it'll always return all columns whereas any other solution reducing the number of columns being returned would force you into a per-row-based solution.