3

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.

2

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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