Re: table design and data type choice - Mailing list pgsql-general
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.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
pgsql-general by date: