Create a Voting database Scheme

Question

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.

Answer 1

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.