Storing Data Part 2: DOCA Example

This is part 2 of a series on storing and managing social science data in relational databases. If you haven’t already, read part 1 to get up to speed.

To help illustrate some of the concepts introduced in Part 1, I’m going to use the Dynamics of Collective Action (DOCA) dataset to design an efficient relational database. You can download the entire dataset at the DOCA website. For our purposes, I’m going to refer to a 15 case sample, which you can view here. The DOCA dataset consists of data coded from newspaper coverage of protest events. Each case is a distinct protest event.  Table 1 contains a description of each of the over 100 variables in the dataset. For more detailed description of the dataset, you can browse the DOCA website.

Table 1: DOCA Variables
variable description
coder name of the coder
date date the event was coded
newsnm name of the newspaper the event was coded from
rptmm, rptdd, rptyy date (month, day, and year) the newspaper article was published
evmm, evdd, evyy date (month, day, and year) the event took place
days number of days the event lasted
eventid unique ID number of event
stories number of articles on event
title title of the newspaper article
followup (previous month?)
who, what, where, against, purpose, whysm Brief narrative describing protest event
page, section page and section of newspaper the article appears in
staten number of states the event took place in
state1, state2, state3, state4 list of states the event took place in (only first four)
cityn number of cities the event took place in
city1, city2 first two cities in list of cities event took place in
neighn number of neighborhoods the event took place in
neighbr primary neighborhood the event took place in
particd was the number of participants reported?
particex if yes above, how many participants were reported?
partices if no above, what is the most appropriate estimate of participants (from a list)
groups number of groups (broadly defined identity groups) initiating event
igrp1c1, igrp1c2, igrp2c1, igrp2c2, igrp3c1, igrp3c2 the types of groups involved in the event, with the primary group listed first, and so on.
targd was there an identifiable target of the event?
targnum how many targets did the event have?
targ1, targ2 list of target types
ertarg1, ertarg2 list of target types of ethnic events
smonamed were specific organizations named in the article?
smonum how many organizations were named
smoname1, smoname2, smoname3, smoname4 list of named organizations
claim1, val1, claim2, val2, claim3, val3, claim4, val4 list of claim topics made in the event (claim1) along with whether the claim was pro- or anti- the claim topic (val1)
form1, form2, form3, form4 list of forms the event appears as (e.g. picket, civil disobedience, symbolic display)
act1, act2, act3, act4 list of main behaviors used by the protesters
viold did protesters use violence?
violtype what kind of violence did protesters use?
counterd were counter demonstrators present?
police1 where police mentioned at the event?
police2 did police take action at the event?
police3 did the police use physical force?
police4 did the police use violence?
arrestd where there arrests at the event?
arrestex number of arrests reported (Exact number)
arrestes if exact number of arrests not reported, best estimate (from list)
propdam was there property damage?
dollars dollar amount of property damage
injury was anyone reported as injured?
deaths was anyone reported as killed?
injprot, injprot_est protesters injured, reported and estimated
injby, injby_est bystanders injured, reported and estimated
injpol, injpol_est police injured, reported and estimated
injoth, injoth_est other injured, reported and estimated
dthprot, dthprot_est protesters killed, reported and estimated
dthby, dthby_est bystanders killed, reported and estimated
dthpol, dthpol_est police killed, reported and estimated
dthoth, dthoth_est other killed, reported and estimated
agenda_code Policy Agendas Project topic code

You’ll notice immediately that there are a lot of variables in this dataset – 104 in total. You’ll also notice that many variables are repeats – these are listed in the same cell in the table above. This happens when you want to be able to assign more than one category to a case for a specific variable. For example, the claim variable is repeated four times in the spreadsheet because the DOCA coders wanted to be able to assign more than one claim to a single event. This increases accuracy of the coding, but also makes the data more complex. These sorts of issues are where relational databases really excel at storing data efficiently.

When you have multiple instances of the same variable, like claims, storing the data in a spreadsheet becomes inefficient and cumbersome. You have to have have columns for the maximum number of claims you want to be able to assign to a case. So if you want to be able to sign up to six different claims to a case, you have to have six columns in the spreadsheet, even for cases that only have one claim. This is true even if the vast majority of your cases only have one or two claims, resulting in a large number of empty cells. Plus, you are limited to the number of columns you created – you can’t assign seven claims to an event because there’s not a column to store the data for the seventh claim. In the sample we are using here, only one case has more than 2 claims, and no cases have 4 claims. So we have a bunch of empty cells taking up space in our dataset.

A better way to store this data is to have a separate table for claims. The table will have fields for the row id, the event id it is referring back to, the claim type and the claim valence. When done this way, you don’t have a limit on how many claims you can assign to an event. You just add a row to the claims table for each claim you assign to an event – as many as you would like. The table would look something like the table 2.

Table 2: Claims
id event_id claim valence
1 9009029 1900 1
2 9005036 1505 1
3 8407041 1340 3
4 8109077 1400 2
5 7911085 740 1
6 7911085 1000 1
7 7707051 1713 1
8 7503056 1336 2
9 7105082 1400 3
10 7103032 1106 1
11 6905020 1500 1
12 6804168 711 2
13 6706112 1518 1
14 6706112 1500 1
15 6706112 1334 2
16 6505008 1501 1
17 6505008 1500 1
18 6210014 1501 1
19 6210014 1342 3
20 6112002 1334 3
21 6112002 1501 1
22 6011034 1501 2
23 6011034 1501 1

Notice we’ve assigned each row a unique ID number. This is so it is easier to refer to a specific row in case we want to edit or delete data in the future (the id will become more important when we start using MySQL). Each row also contains the event_id for the event the claim corresponds to. You can see that some events have multiple claims, as they did in the original spreadsheet. However, by storing the claims data in a separate table, we eliminate the blank cells for events that only have one or two claims. Instead, we just add a new row whenever there is another claim for an event. This will allow us to code as many claims as we want for each event.

Applying this technique to all the repeating variables, we would create 9 new tables: states, cities, neighborhoods, groups initiating, targets, smos named, claims, event forms, and acts.

We could also create a table for injuries and deaths, with a field for reported or estimated, a field for person type (protesters, bystanders, police, other), and a field for injury type (injury or death).

This google spreadsheet contains the DOCA data reorganized into separate tables as I described. Each sheet contains a separate table. The data relates back to each other through the event_id variable, common to all tables. Notice how few empty cells there are in each table. This is not necessarily the best way to organize a relational database around the DOCA data, but it is a good illustration of basic concepts we’ve already learned.

Let’s recap what we’ve learned so far:

First, relational databases are good at storing multi-leveled data. In general, when designing relational databases, you want to avoid repeating data across rows in a single table. If you find yourself inputting the exact same data (say, org name, or school, or location information), think about how that data could be stored in a separate table and referred to through a simple id.

Second, relational databases are good for tagging multiple categories or codes for a single case. Instead of creating redundant columns in a single table, move the categorization into its own table – multiple categories become multiple rows instead of multiple columns. Rows are much easier to add than columns.

Next, we’ll get more hands on and learn how to create databases in MySQL.