Understanding Election Databases-- a Primer

This article would give you an idea about how election data are organised into a databases. To actually develop and use databases you would need additional learning/training. (The discussion is in the context of electoral system in India.)

From the schools days, one would be aware of the usefulness of tables in organising data. Databases too use tables, consisting of rows and columns, to organise data. So, election data such as the constituency number, constituency name, electorate, votes polled, rejected votes and details about candidates such as name, party and votes secured could be organised into a table. An untrained mind may suggest the organisation of the data into columns in following manner.

Constituency number, constituency name, electorate, votes polled, rejected votes, candidate 1, party, votes secured, candidate 2, party, votes secured, candidate 3 ...

As rows are added, it would become obvious that several cells would remain empty as the number of candidates vary from constituency to constituency. So, it would be inefficient and somewhat unwieldy to organise data this way.

The solution used by database developers is to split the table and set a relationship between the resulting tables.
Here constituency number, constituency name, electorate, votes polled, rejected votes could form one table.
Another table can have constituency number, candidate name, party and votes as its columns.

As constituency number is common for both the tables, it could be used to set a relationship between the tables or join the tables in a database. Databases have commands such as JOIN to achieve this.

CNo
 Constituency
 Electorate
 Votespolled
 Invalid
1
 KASARAGOD 1199964 931632 5555
2
 CANNANORE 1121192 886354 5454
3
 BADAGARA 1155934 861933 555
4
 CALICUT 1124640 826901 7776
 
  
 
  
  

 
 
  
  
 
 
 
  
  
CNo
 Name
 Party
 Votes
1
 T. GOVINDAN CPM
 423564
1
 KHADER MANGAD INC
 391986
1
 P. K. KRISHNADASAN BJP
 101934
2
 A. P. ABDULLAKUTTY CPM
 428390
2
 M. RAMACHANDRAN INC
 418143
2
 N. HARIHARAN JDU
 26069
3
  
  
  

Let us look at the first table closer.

The constituency number is usually designated as the primary key (for reference to rows while working on tables and joining them). and indexed (for fast retrieval). A primary key is a column or set of columns that uniquely identifies the rest of the data in any given row.

In the second table below the constituency number is the key for joining the table with primary key in the first table.

Note that data in each table deals with single entities (constituency and candidates). Detailed information about them can be obtained by joining the tables.

When the join is effected, the matrix that could form in computer memory could be imagined as something like the following. (There is more than one way of joining the tables. Rows can be used selectively).

CNo
 Constituency
 Electorate
 Votespolled
 Invalid

 Party
 Votes
1
 KASARAGOD 1199964 931632 5555
T. GOVINDAN CPM 423564
1
 KASARAGOD 1199964 931632 5555 KHADER MANGAD INC 391986
1
 KASARAGOD 1199964 931632 5555 P. K. KRISHNADASAN BJP
 101934
2
 CANNANORE 1121192 886354 5454
A. P. ABDULLAKUTTY CPM 428390
2
 CANNANORE 1121192 886354 5454 M. RAMACHANDRAN INC 418143
2
 CANNANORE 1121192 886354 5454 N. HARIHARAN JDU
 26069

The Joining is done over a condition. Here the condition could be the equality of constituency numbers in the first and second tables (table1.cno=table2.cno).

With or without joining, tables can be queried to obtain the information you want in the required order. A Query Language such as SQL is used for this purpose. Programming languages like PHP can be used to query a database.

Tips for organising data:

In the current Indian context, it would be necessary to include information about the front to which a party belongs. The principles of database design mandate that tables should be organised to minimise the disk space used and the typing required to enter data.

So, it has to be set up as a third table consisting of the columns parties and front. However, if it is included in the second table, that would simplify many things for beginners as information such as total votes polled by a front can be obtained through simple queries (without joining tables).

In the second table, a column containing serial number of rows may become necessary for certain operations like updating. This (alone or with the constituency number) could be the primary key and this is usually created by auto incrementing the values in the column as rows are added.

Election Commission of India uses State wise constituency numbers. So, if you want to combine data pertaining to more than one State, you have to devise a key combining a State Code and constituency numbers. (See tips on Census data.) This may complicate programming required to retrieve data. An alternative is to have a separate column containing State code and the two could be combined to form the primary key. (A third table containing the State code and name of the State will naturally be required.)

BACK