Mysql – Table structure for voting app

database-designMySQL

I have an app where a user votes between two local profiles on which one is better. I want to ensure that the same user cannot vote more than once between the same two profiles (say the user votes on two different days or something), but I'm not sure what the most efficient way is to do this.

A simple solution is to have a table with 3 fields: voter (the user #), winner (the profile # who won), and loser (the profile # who lost). not necessarily gonna use those names, but I do want to use the data on who won/lost.

Is it horribly inefficient and stupid to send a request for every new matchup to search that table to see if the voter/votees have already been processed? If not, what are my options? Is it really that huge of a deal? Or should I instead allow repeat matchups and simply update the table fields accordingly if the voter's choice changes?

Best Answer

Here's how I would model the tables for that requirement (note the table and column names can be changed for whatever is appropriate for your application):

enter image description here

In this model, the idea of a "Profile" and "User" is contained in the same entity, "User". The idea of a single vote for one of two user candidates is contained in the entity "Vote". If you want to restrict a User to one Vote per set of two users, then you can add a unique index in Vote for idUser, idUserCandidate1, and idUserCandidate2.

Related Solutions

How to segment account info and profile info

Unless there is a security problem (you want someone to view some information, but not the other), there is no need to; however in many sql platforms you can deny access to specific fields in security or just create a view. The only other reason you would want to have two tables is if you have a business reason to segregate the data to two separate tables. Otherwise, if you are constantly needing to get information from table A and table B at the same time, you have to join each time which will add to overhead.

Mysql – Counting on Two Different Columns in the Same Table

John,

If you have a table of users, and a table of matches, you should probably have a reference table between the two of them, that has a many-to-many relationship.

users:
--------------------
id (PK)       Int
display_name  VarChar(250)

matches:
---------------------
matchId (PK)   Int
...
...
  -- Other relevant attributes about the match

results:
---------------------
ResultId (PK)      Int
Name           Varchar(100) 
  -- Win, loss, whatever else

match_results:
---------------------
matchId (PK, FK)  Int
UserId (PK, FK)   Int
ResultId (PK, FK) Int

For every match, you should have a record for every participant in that match, and their relevant result. Unless you plan on having more than 2 billion matches or users, I would stick to using Int.

Now you can join across these tables and SUM() or MAX() or whatever aggregate you would prefer.

SELECT u.Name , 
   COUNT( CASE
          WHEN r.Name = 'Win' THEN 1
          END )AS Wins , 
   COUNT( CASE
          WHEN r.Name = 'Loss' THEN 1
          END )AS Losses
FROM match_results AS mr INNER JOIN users AS u ON mr.userId = u.id
                       INNER JOIN matches AS m ON mr.matchId = m.id
                       INNER JOIN results AS r ON mr.resultId = r.id;

Edit:

Using your existing schema, you can use sub queries to aggregate your statistics

  SELECT u.display_name AS NAME, 
    (SELECT COUNT(1) FROM match_results mr WHERE mr.winner_id = u.id) AS Wins,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.loser_id = u.id) AS Losses
  FROM users u

Best Answer

Related Solutions

How to segment account info and profile info

Mysql – Counting on Two Different Columns in the Same Table

Related Question