Database Design – Voting System

cdatabase-design

I'm creating a website in which people can submit there own lists, and then essentially others Up/Down Vote the items within the list. I'd like the list (If the submitter allows "votes") to be ordered by the amount of votes (Easy Enough to do in SQL I know).

However, my issue is with the DB Design, I don't know how to design it to make it work.

So far my Idea is:

Lists Table: ListID, ListDescription, ListItem(x [e.g. 1, 2, 3 4]), CreatedDate, UserID (if it is user submitted).

Votes Table: VoteID, ListID

I can't quite get my head round, how to setup the votes table to record votes for each item in the specified list?

Could anyone help please?

Thanks

Best Answer

Not quite sure about what you're after, but you want a list that is created (or not) by one user and this list can have multiple items. The votes on each of the item will determine what position they are on the list?

I can think of it like this:

The table Lists contains the data of the list itself and the table ListItems contains the ListID, the Item and the Votes

Lists: ListID, UserID, ListDescription, CreatedDate
ListItems: ListID, ItemID, Votes

ListTable with the data of the List, ListItems with the data of the Items and one initial position for each item and a Votes table, voted by one UserID.

List: ListID, UserID, ListDescription, CreatedDate
ListItems: ItemID, ListID, InitialPosition
Votes: VoteID, UserID, ItemID

Correct me if I didn't understand correctly the problem.

Related Solutions

How to store several lists and their items

It sounds like you want three tables

PROJECT( project_id, <<other attributes>>)
LIST( list_id, project_id, <<other attributes (e.g. the name of the list)>> )
LIST_ITEMS( list_item_id, list_id, <<other attributes>>)

Combining the LIST and LIST_ITEM tables wouldn't be appropriate because then you'd be storing attributes of the list (i.e. the list's name and description) in multiple rows of the LIST_ITEM table rather than just once in a single row of the LIST table.

Sql-server – How to store immutable, unique, ordered, lists

I would go with a traditional set-based approach for this. Create entity, list, entityList, and listItem tables to store your data. Individual list and entity tables allow you to create lists without entities and vice versa. Constraints on these tables such as unique constraints and foreign keys will help preserve the integrity of the data.

For the list, create a single stored procedure which is the single point for inserting lists and listItems. This proc has logic to detect if a list already exists and serves as a gateway. This is straightforward to do using set operators like EXCEPT.

Here's a simple demo which shows how something like this might work. I'm using a table-valued parameter (TVP) to handle passing around lists and a scalar function to determine if a list already exists. Scalar functions are evil when used in resultsets (eg SELECT, WHERE clauses, JOIN ) but are ok when used for what they are meant for, returning single values. Spend some time working through it and see if it could work for you:

USE tempdb
GO

SET NOCOUNT ON
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

-- Reset
IF OBJECT_ID('dbo.entityLists') IS NOT NULL DROP TABLE dbo.entityLists
IF OBJECT_ID('dbo.listItems') IS NOT NULL DROP TABLE dbo.listItems
IF OBJECT_ID('dbo.entities') IS NOT NULL DROP TABLE dbo.entities
IF OBJECT_ID('dbo.lists') IS NOT NULL DROP TABLE dbo.lists
IF OBJECT_ID('dbo.usp_addList') IS NOT NULL DROP PROC dbo.usp_addList
IF OBJECT_ID('dbo.usf_listExists') IS NOT NULL DROP FUNCTION dbo.usf_listExists
IF EXISTS ( SELECT * FROM sys.types st INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'tvp_myList' AND ss.name = N'dbo')
DROP TYPE dbo.tvp_myList
GO


CREATE TABLE dbo.entities
(
    entityId        INT IDENTITY,
    entityName      VARCHAR(50) NOT NULL,

    dateAdded       DATETIME    NOT NULL DEFAULT GETDATE(),
    addedBy         VARCHAR(30) NOT NULL DEFAULT SUSER_NAME(),
    dateModified    DATETIME    NULL,
    modifiedBy      VARCHAR(30) NULL,

    CONSTRAINT PK_entities PRIMARY KEY ( entityId ),
    CONSTRAINT UK_entities__entityName UNIQUE ( entityName )

)
GO

CREATE TABLE dbo.lists
(
    listId          INT IDENTITY(1000,1),
    listName        VARCHAR(50) NOT NULL,

    dateAdded       DATETIME    NOT NULL DEFAULT GETDATE(),
    addedBy         VARCHAR(30) NOT NULL DEFAULT SUSER_NAME(),
    dateModified    DATETIME    NULL,
    modifiedBy      VARCHAR(30) NULL,

    CONSTRAINT PK_lists PRIMARY KEY ( listId ),
    CONSTRAINT UK_lists__listName UNIQUE ( listName )

)
GO


-- Although constraints might appear a little excessive, they prevent 'bad data' getting into the table,
-- whereas lesser constraints would not, eg prevent list with duplicate order
CREATE TABLE dbo.listItems
(
    listItemId      INT IDENTITY(100000,1),
    listId          INT NOT NULL,
    itemOrderId     INT NOT NULL,
    itemValue       INT NOT NULL,

    CONSTRAINT PK_listItems PRIMARY KEY ( listItemId ),         -- prevent list with duplicate order, value
    CONSTRAINT UK_listItems__listId__itemValue UNIQUE ( listId, itemValue ),        -- prevent list with duplicate value
    CONSTRAINT UK_listItems__listId__itemOrderId UNIQUE ( listId, itemOrderId ),    -- prevent list with duplicate order
    CONSTRAINT UK_listItems UNIQUE ( listId, itemOrderId, itemValue ),

    CONSTRAINT FK_listItems__listId FOREIGN KEY ( listId ) REFERENCES  dbo.lists ( listId )

)
GO

CREATE INDEX _idx_listItems__itemOrderId__itemValue ON dbo.listItems ( itemOrderId, itemValue ) -- to be used in list existence check 
GO


CREATE TABLE dbo.entityLists
(
    entityId        INT NOT NULL,
    listId          INT NOT NULL,

    CONSTRAINT PK_entityLists PRIMARY KEY ( entityId, listId ),
    CONSTRAINT UK_entityLists__entityId UNIQUE ( entityId ),
    CONSTRAINT UK_entityLists__listId UNIQUE ( listId ),
    CONSTRAINT FK_entityLists__entityId FOREIGN KEY ( entityId ) REFERENCES  dbo.entities ( entityId ),
    CONSTRAINT FK_entityLists__listId FOREIGN KEY ( listId ) REFERENCES  dbo.lists ( listId )

)
GO


-- Create a type for passing lists around; TVP types and constraints mirror target table to act as a quality control
CREATE TYPE dbo.tvp_myList AS TABLE
(
    itemOrderId INT NOT NULL UNIQUE,    -- you could make this an identity column if you want to make sure order is sequential
    itemValue   INT NOT NULL UNIQUE,

    PRIMARY KEY ( itemOrderId, itemValue )

)
GO

-- Scalar function to determine if list already exists
CREATE FUNCTION dbo.usf_listExists
(
    @tvp        AS dbo.tvp_myList   READONLY
)

RETURNS BIT
AS
BEGIN
    DECLARE @listExists_yn  BIT

    IF EXISTS (
        SELECT COUNT(*) OVER() totalItems, itemOrderId, itemValue
        FROM @tvp
        EXCEPT
        SELECT COUNT(*) OVER( PARTITION BY listId ) totalItems, itemOrderId, itemValue
        FROM dbo.listItems
        )
        SET @listExists_yn = 0
    ELSE
        SET @listExists_yn = 1

    RETURN @listExists_yn

END
GO

-- Create a proc to add lists, report if list already exists
CREATE PROC dbo.usp_addList

    @listName   VARCHAR(50) = NULL,
    @tvp        AS dbo.tvp_myList   READONLY,
    @listId     INT OUTPUT

AS

SET NOCOUNT ON

    BEGIN TRY

        -- Check if list name already exists
        IF @listName IS NULL
            SET @listName = NEWID()
        ELSE
        IF EXISTS ( SELECT * FROM dbo.lists WHERE listName = @listName ) 
            RAISERROR ( 'List name ''%s'' already exists id %s', 16, 1, @listName );


        -- Check if list content already exists
        IF dbo.usf_listExists(@tvp) = 1
        BEGIN
            -- Get list
            --!!TODO add 'get list id' proc
            RAISERROR ( 'List already exists id %i', 16, 1, @listId );
        END
        ELSE
        BEGIN

            BEGIN TRAN

                -- Add the list
                INSERT INTO dbo.lists ( listName ) VALUES ( @listName )
                SET @listId = SCOPE_IDENTITY()

                -- Add the list items
                INSERT INTO dbo.listItems ( listId, itemOrderId, itemValue )
                SELECT @listId, itemOrderId, itemValue
                FROM @tvp

            COMMIT TRAN

        END

    END TRY
    BEGIN CATCH

        DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE()

        IF @@TRANCOUNT > 0 ROLLBACK
        RAISERROR( @errorMessage, 16, 1 )

    END CATCH

    RETURN

GO

-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Initial Data START
-- Add some data; actually entities are irrelevant to this demo
------------------------------------------------------------------------------------------------

;WITH cte AS (
SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.entities ( entityName )
SELECT 'Entity ' + CAST ( rn AS VARCHAR(10) )
FROM cte
GO

---- Add some simple lists
DECLARE @listId INT, @tvp AS dbo.tvp_myList

INSERT INTO @tvp VALUES ( 1, 10 )   -- Single Item List ( 10 )

EXEC dbo.usp_addList 'my New List 1', @tvp, @listId OUT

INSERT INTO @tvp VALUES ( 2, 20 )   -- Two Item List ( 10, 20 )
EXEC dbo.usp_addList 'my New List 2', @tvp, @listId OUT

INSERT INTO @tvp VALUES ( 3, 30 )   -- Three Item List ( 10, 20, 30 )
EXEC dbo.usp_addList 'my New List 3', @tvp, @listId OUT
GO

-- Initial lists
select * from dbo.lists
select * from dbo.listItems


-- Initial Data END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Test Scenarios START
------------------------------------------------------------------------------------------------

-- Try and add any of those lists again
BEGIN TRY

    -- Single item list already exists
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 )
    EXEC dbo.usp_addList 'my New List 4', @tvp, @listId OUT

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Two item list already exists
    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 )
    EXEC dbo.usp_addList 'my New List 5', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
    EXEC dbo.usp_addList 'my New List 6', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO


BEGIN TRY

    -- New four-item list
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 ), ( 4, 40 )
    EXEC dbo.usp_addList 'my New List 7', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO


BEGIN TRY

    -- New four-item list
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 ), ( 4, 41 )
    EXEC dbo.usp_addList 'my New List 8', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO


BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Two item list does not exist, but three-item list with same two components does exist; both get added
    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 21 ), ( 3, 30 )
    EXEC dbo.usp_addList 'my New List 9', @tvp, @listId OUT
    PRINT @listId

    DELETE @tvp WHERE itemValue = 30
    EXEC dbo.usp_addList 'my New List 10', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO



-- Final lists
SELECT * FROM dbo.lists
SELECT * FROM dbo.listItems
GO

-- Test Scenarios END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Load Tests START
-- For 1,000 random entities, create a randomn-size list and add; report errors
------------------------------------------------------------------------------------------------

-- Load test the addList proc
DECLARE @i INT = 0

WHILE @i < 1000
BEGIN

    DECLARE @item INT, @items INT
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Initialise
    SELECT @items = ( RAND() * 9 ) + 1, @item = 1   -- random number between 1 and 10
    DELETE @tvp

    PRINT @items

    WHILE @item <= @items
    BEGIN

        PRINT CHAR(9) + CAST( @item AS VARCHAR(5) )

        INSERT INTO @tvp VALUES ( @item, RAND() * 10000 )

        SET @item += 1

    END

    EXEC dbo.usp_addList NULL, @tvp, NULL

    SET @i += 1

END
GO



-- Load Tests END
------------------------------------------------------------------------------------------------


-- Visualise your lists
SELECT 
    l.listId,
    l.listName,
    l.dateAdded,
    (
    SELECT
        li1.listId  "@listId",
        COUNT(*) AS "@count",
        (
        SELECT
            itemOrderId AS "@itemOrderId",
            itemValue AS "@itemValue"
        FROM dbo.listItems li2
        WHERE li1.listId = li2.listId
        ORDER BY itemOrderId
        FOR XML PATH('listItem'), TYPE
        )
    FROM dbo.listItems li1
    WHERE l.listId = li1.listId
    GROUP BY li1.listId
    FOR XML PATH('listItems'), TYPE
    ) listItems
FROM dbo.lists l
GO




-- END
------------------------------------------------------------------------------------------------

I'm a big proponent of XML but it is the wrong choice for this design. XML can be appropriate for semi-structured, but your design is actually really structured. The overhead of constantly shredding XML for comparison would be too much, plus you can't directly compare once piece of XML with another easily in SQL Server. Where XML would be useful here, is visualising your lists, as I've done at the end of my demo.

HTH

Best Answer

Related Solutions

How to store several lists and their items

Sql-server – How to store immutable, unique, ordered, lists

Related Question