Create a Voting database Scheme

database-design

I have the following tables:

create table dbo.Places (
  Id int identity not null primary key clustered (Id),            
  Name nvarchar (100) not null
)

create table dbo.Posts (
  Id int identity not null primary key clustered (Id),            
  Name nvarchar (100) not null
)

create table dbo.Users (
  Id int identity not null primary key clustered (Id),            
  Name nvarchar (100) not null
)

I would like to register Votes made by Users in Places and Posts.

Each Vote has one Value and the CreatedDate so I can track the votes by time.

Which scheme would you advice for this case? A two table scheme:

create table dbo.PlacesVotes (
  PlaceId int not null,
  UserId int not null,
  CreatedDate datetime not null,
  Value int not null      
)

create table dbo.PostsVotes (
  PostId int not null,
  UserId int not null,
  CreatedDate datetime not null,
  Value int not null      
)

Or three tables:

create table dbo.PlacesVotes (
  PlaceId int not null,
  UserId int not null,
  VoteId int not null
)

create table dbo.PostsVotes (
  PostId int not null,
  UserId int not null,
  VoteId int not null
)

create table dbo.Votes (
  Id int not null,
  CreatedDate datetime not null,
  Value int not null         
)

Where PostsVotes and PlacesVotes work only as a junction table.

In this moment I am voting only Places and Posts but I will need to add votes to other tables in the future.

A user can vote for multiple places and multiple posts. However can only vote once for each place or each post.

Best Answer

Assuming a person can only submit one and only one vote, you just need two tables, PlaceVotes and PostVotes, as follows

create table dbo.PlaceVotes (
 Id int not null,
 PlaceId int not null,
 UserId int not null,
 CreatedDate datetime not null,
 Value int not null,
 constraint unique index ux_userid (UserId, place),
 constraint primary key pk_placevotes(id)
)

create table dbo.PostVotes (
 Id int not null,
 PostId int not null,
 UserId int not null,
 CreatedDate datetime not null,
 Value int not null,
 constraint unique index ux_userid (UserId, postid),
 constraint primary key pk_postvotes(id)
)

This way, the voting event is represented as one entity.

Related Solutions

Sql-server – SQL Server Primary key / clustered index design decision

You are correct to separate "clustered index" from "primary key":

A clustered index is the organisation of data on disk is better if
- narrow
- numeric
- increasing (strictly monotonic)
The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

ProductHistoryID
ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

clustered index should be on ProductHistoryID
unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Best Answer

Related Solutions

Sql-server – SQL Server Primary key / clustered index design decision

Sql-server – Do I need a separate Id column for this “mapping” table

Related Question