Here is one approach:
CREATE TABLE columns (
id INTEGER,
ordinal INTEGER);
CREATE TABLE rows (
id INTEGER,
ordinal INTEGER);
CREATE TABLE cells (
rowid INTEGER,
columnid INTEGER,
value TEXT);
This way, you will still need to +1/-1 the ordinals behind the new/deleted position, but fortunately you can do it all with a single statement, UPDATE rows SET ordinal = ordinal + 1 WHERE ordinal > 42. Although it updates many rows, the update statement should execute in less than a second.
Benefits:
- Updating a rows table with N rows is lighter than updating a cells table with N*M rows.
- Explicitly storing the ordinal means fast random access to an ordered subset of the data.
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.
Best Answer
Which solution you chose is largely a matter of personal preference. I have a strong preference for the foreign key approach, and think I can provide good reasons why.
First of all, check that your requirement is strictly to capture a single "primary supplier", and not (potentially multiple) "preferred suppliers". If you can't guarantee that you'll never have more than 1, the foreign key approach will break and you're better off going with the flag approach from day 1.
If, however, a part always has a single primary supplier I would use the foreign key approach. If a part must have a primary supplier, then this is the only approach where the database constraints guarantee that the data is always correct -- make the foreign key column mandatory (not null) and you will have to provide the primary supplier when adding the part. There is simply no way that the data cannot be correct.
With the flag approach, the primary supplier information can be missing from parts -- unless you want to rely on your application's logic. If that's potentially a problem, the mandatory foreign key approach is the easiest way to ensure this can never happen.
Think also about updates. With the flag approach, you need to clear the flag from other "part supplier" records when you change preferred suppliers.