8
\$\begingroup\$

I have the following tables structures:

CREATE TABLE IF NOT EXISTS `options` (
  `option_id` tinyint(4) unsigned NOT NULL,
  `poll_id` mediumint(8) unsigned NOT NULL,
  `option` tinytext NOT NULL,
  UNIQUE KEY `option_id_poll_id` (`option_id`,`poll_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Storing poll options/choices';

CREATE TABLE IF NOT EXISTS `questions` (
  `poll_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `question` tinytext NOT NULL,
  `nick` varchar(32) NOT NULL,
  `dated` datetime NOT NULL,
  PRIMARY KEY (`poll_id`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table to store poll questions/titles';

CREATE TABLE IF NOT EXISTS `votes` (
  `poll_id` mediumint(8) unsigned NOT NULL,
  `option_id` tinyint(4) unsigned NOT NULL,
  `nick` varchar(32) NOT NULL,
  `dated` datetime NOT NULL,
  UNIQUE KEY `poll_id_nick` (`poll_id`,`nick`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I am displaying details for a particular poll_id using the following query:

SELECT
    o.option_id AS option_id,
    o.`option` AS `option`,
    COUNT(v.nick) AS total,
    FLOOR(
        ( COUNT(v.nick) / (SELECT COUNT(1) FROM votes WHERE poll_id = %d) ) * 100
    ) AS percentage
FROM options o
LEFT JOIN votes v
    ON o.poll_id = v.poll_id
    AND v.option_id = o.option_id
WHERE o.poll_id = %d
GROUP BY o.option_id
ORDER BY total DESC, o.option_id ASC;

I am not satisfied with the way that I am calculating the percentage votes on each of the available options! %d is place-holder for string.format function.

I'd like a review on the query/table structures above.

\$\endgroup\$
3
\$\begingroup\$

It took me a while to realize that you must be considering a poll to be a single question (posted on a web page) that allows people to pick from a series of options for that single question.

Some things to consider:

  • [options] Consider reversing the index field order. It would seem probable that you will want to query up the options for a poll without specifying the option_id.

  • [questions] There appears to be no real point to indexing the deleted bit. When pulling questions up for a poll you can certainly exclude those that are deleted without needing an index.

  • [votes] I'm guessing that 'nick' is the login name of the user choosing an option from a single question poll?

  • Look at an explain to see if MySQL has optimized your sub-query into a single lookup. If not, do your own explicit query or consider using group by modifiers to do a rollup when you pull up poll stats. Then, in either case, as you load records for a poll (as you'll have some code for web display purposes) do the percentage calculation as you generate the output table.

Finally, it is customary to "select count(*) from tab" when you don't care about the columns involved.

\$\endgroup\$

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.