MySQL database-design question

Question

Situation
Say I have the following:

• a table items
• a table lists to which these items can be linked
• a table users

Condition: a list can belong to all or one specific user

What I'd like to do is create "global" lists that exist for ever user. But there should be a possibility to "overwrite" this global list with a user-specific one. Example for a random user:

• a global list called books
• a user-specific list cds

Now when retrieving the lists for this user I should get both the global books lists and the user-specific cds list. But what if this user has a specific list of books?

• a global list called books
• a user-specific list books
• a user-specific list cds

So now it should retrieve the user-specific list called books instead of the global list, and of course the user-specific cds list.

Possible solutions
The easiest thing would be something like this:

table: lists
- id
- name
- userId

If the userId IS NULL it would mean that it's a global list, but when it's NOT NULL it belongs to a specific user. So the first example would be for userId = 1:

id  name    userId
1   books   NULL
2   cds     1

To retrieve the lists for this user:

SELECT * FROM lists WHERE userId = 1 OR userId IS NULL

But then I'm stuck at the next part:

id  name    userId
1   books   NULL
2   cds     1
3   books   1

Here the first list "books" is a global one, but this user also has a user-specific list "books". It is possible to ignore the global list in this case, and only return the user-specific one?

Another possibility would be to create a lists_users table, linking all the users to all the global lists, and deleting this link whenever a user-specific list is created with the same name as one of these global lists. This doesn't seem very efficient though, and a lot more work to maintain when adding new users.

Question:
What's the best and most efficient way to set this up in the database, so that I can easily retrieve the correct lists for a specific user?

Answer 1

For your current design you can get user's list in the following way:

SELECT * FROM lists 
WHERE userId = 1 
UNION ALL
Select * from lists g 
Where userId IS NULL
AND NOT EXISTS (SELECT 1 from lists l where g.name = l.name and userid = 1)

i.e. select user-specific lists and global not-redefined by user

Answer 2

What if you were to filter items instead of lists?

Select distinct   -- items from user-specific lists 
      ItemName
    , ItemType
from User     as u
join UserList as x on x.UserId = u.UserId
join List     as t on t.ListId = x.ListId
join ListItem as y on y.ListId = t.ListId
join Item     as m on m.ItemId = t.ItemId
where ListType = 'custom'
and u.UserId   = some_user_id
UNION
Select distinct 
      ItemName  -- items from global lists
    , ItemType
from List     as t on t.ListId = x.ListId
join ListItem as y on y.ListId = t.ListId
join Item     as m on m.ItemId = t.ItemId
where ListType = 'global'

order by ItemType, ItemName
;

Answer 3

in my very personal opinion, when you start playing with NULL values in the db to solve a conception problem it's because you need to change your tables..

i propose this: (sorry for the messy UML diagram)

the idea behind this is that you could use "heritage" to separate the two type of lists: UserDefinedList and DefaultLists. As you can see, only UserDefinedLists are related to Users and DefaultLists are open to any user who wants to use them. Of course, when someone wants to create a list books you could tell the user that the list has been created but actually you don't insert anything in the database.

When you want to display the lists of an user, you display the UserDefinedLists and all the DefaultLists.

Well it might sound confusing but the moral of the story is that Heritage is useful in Databases when you have different types of elements (like Lists) and each type has different relations to the other tables (i.e. UserDefinedLists are related to Users and DefaultLists are not, but they are both types of List)

Hope this helps