MySQL database design task

Question

Situation
Let's say I have the following:

• table items
• table listswith which these items can be associated
• table users

Condition: the list may belong to all or one specific user

I would like to create global lists that exist forever. But it should be possible to "overwrite" this global custom list. Example for a random user:

• global list called books
• custom cds list

Now, getting lists for this user, I should get both lists of books around the world, and a list of accounts for a specific user. But what if this user has a specific list of books?

• global list called books
• cds

, , , , , , -, .

- :

table: lists
- id
- name
- userId

userId IS NULL , , NOT NULL . , userId = 1:

id  name    userId
1   books   NULL
2   cds     1

:

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

:

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

"" , "" . ?

- lists_users, , , . , .

:
, ?

Answer 1

:

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)

.

Answer 2

, ?

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

, NULL db, , .

: ( UML)

, "" : UserDefinedList DefaultLists. , UserDefinedLists , , . , - , , , .

, UserDefinedLists .

Well, this may seem strange, but the moral of this story is that Heritage is useful in Databases when you have different types of items (like lists) and each type has a different relationship with other tables (like UserDefinedLists are associated with users and DefaultLists are not, but they are both List types)

Hope this helps