table design and data type choice

On Wed, Jan 8, 2014 at 3:11 PM, Jayadevan M <maymala.jayadevan@gmail.com>wrote:

We have a table to record the voteup/votedown by users of questions and
answers (like on stackoverflow). So there will be a large number of inserts
(voteup/down), some updates(user changes mind)and may be a few deletes. The
queries will be mostly aggregates (count(*) where question_id=<x> and
vote_up =1) . Is it better to have data type of Boolean, varchar or int?

I assume there isanother decision too - have 2 columns - one for up and
one for down, or have just one column which will be 1 or -1.

This is more of a design call. I would go with approach of having one
column.
But when you talk about voting a vote could either be up or down? Why will
you need two columns? Can up and down be false at same time? Or both be
true at same time? If they are mutual exclusive you should consider having
one column.

If the queries are always going to be based on vote='up' or vote=1 or
vote=t (?), then you can use partial indexes to optimize the access and
updates etc.

http://www.postgresql.org/docs/9.3/static/indexes-partial.html

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350 <%2B65%208110%200350>* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).