Database structure to store the User 's preference of layout order

Question

There are a few options in the webpage layout

1. Latest news
2. Recommend news
3. Followed news
4. History news
5. Most Viewed news

The user can select the order of the layout e.g. they can order the most viewed news to the top of the order.

So I am considering how to store the choice in table that can be convenient for development.

The number of choices are fixed, only these 5 choices The user will frequently update the order

I was thinking of:

create a user_choice table

user_id
latest (nullable , integer)
recommend (nullable , integer)
follow (nullable , integer)
history (nullable , integer)
most_view (nullable , integer)

so , when ever a user register create a record in the table, and whenever update change the row, this approach seems feasible but as well not straight forward to re-order the layout in program

So, are there any better structure ideas?

Thanks for helping

Answer 1

I will create a table layout_order with user id, layout_id and order_id this way is easy add more layout without need add more columns to your table.

When you create a new user you assign a default order.

  user_id     layout_id   order_id
    1            1           1
    1            2           2
    1            3           3
    1            4           4
    1            5           5

Here is an example of UPDATE. You need @user_id, @layout_id and @order_id for that layout.

Here I use variable to create a new rank with a special ORDER BY

SqlFiddle Demo You can check what return just the SELECT inside the JOIN

SET @layout_id = 5;
SET @order_id = 2;
SET @user_id = 1;

UPDATE layout_order L 
JOIN (SELECT  l.*, @rownumber := @rownumber + 1 AS rank
      FROM layout_order l 
      CROSS JOIN (select @rownumber := 0) r
      WHERE user_id = @user_id
      ORDER BY CASE
                    WHEN layout_id =  @layout_id THEN @order_id -- here is the variable
                    WHEN order_id  <  @order_id THEN order_id   -- order doesn't change
                    WHEN order_id  >= @order_id THEN order_id + 1                    
               END

     ) t
  ON L.user_id = t.user_id
 AND L.layout_id = t.layout_id
SET L.order_id = t.rank;

Answer 2

I would move in this direction:

create table user
(   -- your pre-existing user table, this is a stub
    id int auto_increment primary key
    -- the rest
);

create table sortChoices
(   -- codes and descriptions of them
    code int auto_increment primary key,
    description varchar(100) not null
);

create table user_sortChoices_Junction
(   -- intersect or junction table for user / sortChoices
    userId int not null,
    code int not null,
    theOrder int not null,
    primary key (userId,code),  -- prevents dupes
    constraint `fk_2user` foreign key (userId) references user(id),
    constraint `fk_2sc` foreign key (code) references sortChoices(code)
);

The junction table drives it, is flexible, and those that follow don't lock themselves into the same thinking 'there will only ever be 5'

Plus, there is the Data Normalization issue, for the others that prefer CSV values. Here is a write-up I did for that, and ties in to Junction Tables.

So, this is as much for those that follow, as it is for the OP question.