mysqlcountvotingvoting-system

Multiple vote options storing in MySQL table


I have a poll which has an undefined number of options (it can have only 2 options, but it can also have 10 or 20 or more options to choose from). I need to store the current vote count in MySQL table. I can't think of a centralized way of storing them except: Create a field vote_count and store a serialized array of voting options mapped to counts. When new vote data comes in this field is read, unserialized, appropriate values are incremented, then field is written to. This needs 2 queries and there might be 5 or more votes incoming per second.

So I need a way to store voting counts for an unknown number of voting options and be able to quickly access it (I need up to date counts for every option displayed on the voting page) and quickly update it (when new votes come in). It has to be within MySQL table. There is no "upper" limit for the number of voting options.


Solution

  • The normative pattern for handling multi-valued attributes, or repeating values, is to add a second table.

    Consider a purchase order that can have more than one line item on it. We represent the line items in a child table, with a foreign key to the parent in the purchase order table:

    CREATE TABLE `purchase_order` (id int not null, foo varchar(200), ... );
    CREATE TABLE `line_item` (id int not null, order_id int not null, ... ); 
    ALTER TABLE `line_item` ADD FOREIGN KEY (order_id) REFERENCES order(id) ;
    
    
    INSERT INTO purchase_order (id, foo) VALUES (101, 'bar'); 
    
    INSERT INTO purchase_order (id, order_id) VALUES (783, 101);
    INSERT INTO purchase_order (id, order_id) VALUES (784, 101);
    INSERT INTO purchase_order (id, order_id) VALUES (785, 101);
    

    We can get a count of the line items associated with a purchase order, like this:

    SELECT COUNT(1)
      FROM line_item 
     WHERE order_id = 101; 
    

    Or, we can get a count of line items for every purchase order, like this:

    SELECT o.id, COUNT(l.id) AS count_line_itesm
      FROM purchase_order o
      LEFT
      JOIN line_item l
        ON l.order_id = o.id
     GROUP BY o.id 
    

    In your case, what are the entities you need to represent (person, place, thing, concept or event; which can be uniquely identified and you need to store information about.

    I'm having difficulty conceptualizing what entities it is you are need to represent.

    poll -
    poll_question - a single question on a given poll
    poll_question_answer - a possible answer to a question to a given poll question
    voter - 
    ballot - associated with one voter and one poll (?)
    vote - the answer given to a particular poll question
    

    Good database design comes from an understanding of the entities and the relationships, and developing a suitable model.