0

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. enter image description here 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 ******/
2
  • Will the favourite lists be shared among users or each user will have his own lists? Commented May 10, 2015 at 17:54
  • @ypercube each user will have its own lists.
    – xerxes
    Commented May 10, 2015 at 18:22

1 Answer 1

1

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". enter image description here

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. enter image description here

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. enter image description here

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. enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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