Mysql – Give every user 3 default ‘bookmark’ entries

database-designMySQL

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?

Best Answer

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.

Related Question