How to design a main list and sublists schema I am finding it difficult to design a database schema for a list feature. there would be a master list which then can be subsectioned into further lists. 

each user is able to mark a candidate as a favourite candidate and can have many favourite candidates. 

if a candidate is deleted from a list then it should still remain part of favourites, or be part of the main favoirtes list without belonging to a particular list.


a user can mark many candidates as favourite 
a user can have many favourites lists 
a favourites list can have many candidates


the following is what I have come up with so far.

as shown in the above image I am allowing the FavouritesListId to be nullable, this way if a list item is not associated with a certain list this means it is still favourited but not part of a particular list. I want to know if this is the optimal solution or if there is a better design approach available.

and here is the script to the schema:

    USE [Favourites]
GO
/****** Object:  Table [dbo].[Candidates]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Candidates](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Candidates] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FavouritesList]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FavouritesList](
    [Id] [int] NOT NULL,
    [Title] [nvarchar](50) NULL,
 CONSTRAINT [PK_FavouritesList] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FavouritesListItem]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FavouritesListItem](
    [Id] [int] NOT NULL,
    [FavouritesListId] [int] NULL,
    [CandidateId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
 CONSTRAINT [PK_FavouritesListItem] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Users]    Script Date: 10/05/2015 18:43:52 ******/
One issue I have with your current model is that it would be possible for a FavouritesList to contain FavouritesListItems that belong to different Users, which to my understanding of your business logic shouldn't happen. I think a better way would be to store all FavouritesListItems that don't belong to a particular FavouritesList in some entity that is designated as a User's "UngroupedFavouritesList". There are a few ways to create that entity.

Method 1
Make the Title attribute in FavouritesList nullable. If it is null, then it is considered to be the User's "UngroupedFavouritesList".


Method 2

Add an "isUngrouped" flag to the FavouritesList table. If it's set to true, the it's considered the User's "UngroupedFavouritesList". The title attribute of FavouritesList would be null in this case.


Method 3

Make FavouritesList a parent table with UngroupedFavouritesList and NamedFavouritesList as child tables. This is achieved by having the primary key of UngroupedFavouritesList and NamedFavouritesList also be a foreign key to the FavouritesList's primary key.


Method 4

Create an "IFavouritesContainer" interface table, which both FavouritesList and User implement. This is achieved by User and FavouritesList having a foreign key to the IFavouritesContainer table. The FavouritesListItem then has a reference to the IFavouritesContainer that contains it. For ungrouped list items, its IFavouritesContainer would be the User. Note that the idea of implementing interfaces isn't a built-in thing with any RDMS that I know of, I'm just using the terms as an analogy to object oriented programming.


Pros and Cons for each method


    
        Method 1
        Pros:
        
            Simple
        
        Cons:
        
            It's not obvious that a null title means it's an "UngroupedFavouritesList
            It's possible for a User to have multiple "UngroupedFavouritesLists"
        
    
    
        Method 2
        Pros:
        
            Simple
            Clear what is an "UngroupedFavouritesList" and what isn't
        
        Cons:
        
            Storing a title for the "UngroupedFavouritesList", even though it will always be null. This is very minor.
            It's possible for a User to have multiple "UngroupedFavouritesLists"
        
    
    
        Method 3
        Pros:
        
            Clear what is an "UngroupedFavouritesList" and what isn't
            Tables don't contain unnecessary fields
        
        Cons:
        
            It's possible for a User to have multiple "UngroupedFavouritesLists"
            More complex
        
    
    
        Method 4
        Pros:
        
            Tables don't contain unnecessary fields
            The business logic of a User containing only one "UngroupedFavouritesList" but multiple "NamedFavouritesLists" is preserved
        
        Cons:
        
            More complex
            Not obvious what it means for a FavouritesListItem to be contained by Users vs FavouritesList