2

I've got a table users and a table bookmarks. The table bookmarks obviously contains pages the user wants easy access too.

But, there's a new requirement now: every user gets 3 unmutable bookmarks. One of the bookmarks (be it an unmutable one or a user-specific one) is marked as homepage. Always.

I'm thinking about 2 solutions, but I'm not sure which one to choose:

1) Add those 3 records for every user and use triggers or application logic to enter those 3 records automatically when a new user is created.

2) Keep those 3 'records' out of the database altogether and fix it in the application (on the 'bookmarks' page). This solution has a great disadvantage: by default, one of the 3 unmutable bookmarks is selected as the user's homepage. The user can change their homepage to one of the other 2 unmutable bookmarks, or a bookmark of their own. I would have no place to store this data, or I would have to insert the 3 unmutables in case the user makes a change. But that sounds hackish.

What would you do? Is inserting 3 records for all users a design flaw?

  • 4
    "Is inserting 3 records for all users a design flaw?" - I wouldn't see that as a design flaw. Btw: which DBMS are you using? – a_horse_with_no_name Aug 1 '14 at 7:57
  • MySQL. It didn't really feel as a design flaw to me either, but it still feels kinda silly to insert hundres of thousands of almost-similar rows. But it's by far the easiest solution. – Sherlock Aug 1 '14 at 8:00
  • 2
    Your disadvantage of not having a place to store the data is too large. Therefore you need to store the data. Therefore you need to insert hundreds of thousands of almost-similar rows - logic simply dictates it. But I wouldn't be too concerned about it from a data storage perspective, you are probably only talking about 50MB or something. Ensure you keep your default bookmark short (like www.google.com) to save space. Remember that they will only be similar until the users start to change them. – blobbles Aug 1 '14 at 9:32
  • It wasn't a data storage concern; it was more semantics for the most part. Thanks though, I implemented it this way. – Sherlock Aug 1 '14 at 9:43
4

Would you be OK with having a single table that has these three immutable bookmarks, and then using a view that joins this table with the user's real bookmarks?

immutable_bookmarks
-------------------
  id
  name
  URL (or whatever this is a bookmark to)

user_bookmarks
--------------
  id
  name
  user_id
  URL

And then define the view as:

create view user_bookmarks_v as
  select id, name, URL, '-1' as user_id
  from immutable_bookmarks
  union
  select id, name, URL, user_id
  from user_bookmarks

(syntax may not be 100% correct MySQL)

When your application needs to get a user's bookmarks, you can do something like this:

select *
from user_bookmarks_v
where user_id = -1 OR user_id = $USER_ID

This gives you the advantage of having the immutable bookmarks in the database, so that if you ever need to change them, it's very easy, as opposed to having them in the application and needing to roll out an application patch to all installations. You can also change the set of immutable bookmarks by adding or removing records and instantly having them accessible to all users immediately. There's no special triggers or procedures, you do need to add a new table and a new view.

This also has the advantage of not needing to insert 3 records for every user.

The disadvantage is the somewhat ugly (in my opinion) where user_id = -1 to capture the immutable bookmarks. It can probably be refined, I just don't have the time at the moment.


Whoops, I just realised this might not work if users can modify the 3 immutable bookmarks... Hmmm... @Sherlock: let me know if this makes sense, your "edit" requirements were a little unclear and this suggestion might not work for you.

  • 3
    I think your answer is nice. The 3 bookmarks are immutable - as I understand the question. The users can only change their default choice (either one of the 3 immutable bookmarks or one of the their own bookmarks.) – ypercubeᵀᴹ Aug 1 '14 at 16:22
  • 1
    Nice answer. And Ypercube is 100% right; so that means I do have to figure out how to store the 'default choice'. This will get me going though! – Sherlock Aug 1 '14 at 17:32
2

(My apologies for the SQL Server syntax - I don't have MySQL. Please treat as pseudo-code.)

A scheme such as the following should work

create table DefaultBookmark(
     TabNumber  int            not null
    ,URL        nvarchar(2000) not null
)
go

create table Users(
     ID     int identity  not null primary key
    ,Name   nvarchar(200) not null
)
go

create table UserBookmark(
     ID         int identity not null primary key
    ,UserID     int          not null references Users(ID)
    ,TabNumber  int          not null 
    ,unique nonclustered (UserID,TabNumber)

    ,URL        nvarchar(2000)
)
go

with two Stored Procedures providing Business Logic thus:

create procedure GetUserBookmarks(
    @UserID     int
) as begin
    select 
         coalesce(DefaultBookmark.TabNumber,UserBookmark.Tabnumber) as TabNumber
        ,coalesce(DefaultBookmark.URL,UserBookmark.URL)             as URL
    from DefaultBookmark
    left join UserBookmark
       on UserBookmark.TabNumber = DefaultBookmark.TabNumber
      and UserID                 = @UserID
end
go

create procedure SetUserBookmark(
     @UserID    int
    ,@TabNumber int
    ,@Url       nvarchar(2000)
) as begin

    with 
    data as (
        select
             dataIn.UserID
            ,dataIn.TabNumber
            ,dataIn.Url
        from( values (@UserID,@TabNumber,@Url) )dataIn(UserID,TabNumber,Url)
        join Users on Users.ID = dataIn.UserID
    )
    merge UserBookmark
    using data
       on UserBookmark.UserID    = data.UserID
      and UserBookmark.TabNumber = data.TabNumber

    when matched then 
    update set URL = data.URL

    when not matched by target then 
    insert(UserID,TabNumber,URL)
    values(data.UserID,data.TabNumber,data.URL)

    output $action, inserted.*
    ;
end
go

Every user in the system will immediately start seeing the three default bookmarks, personal copies of which they can then edit as they see fit.

  • in mysql we can use just varchar instead of nvarchar – gmsantos Aug 1 '14 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.