Views and SokweDB’s Structure

As mentioned in the Introduction, views are queries that look like tables. As such, they can, and usually do, combine multiple tables into something that looks like a single table.

Views are important to SokweDB because they appear to transform the underlying structure of the database’s tables into something more akin to the traditional paper data collection sheets used to collect data in the field.

To understand what this means we’ll look at grooming and aggression.

The paper data collection sheets have separate columns for the individual doing the grooming and the individual receiving the grooming. Or, similarly, the individual aggressing and the individual aggressed against. These two different types of data could each be stored in separate tables, with columns in the aggressions table for aggressor and aggressee, and columns in the grooming table for groomer and groomee.

This organizational structure works well when one wants to query each of these behaviors in isolation. It begins to break down when querying across data sets, particularly as the number of data sets increases beyond a few.

To ask a simple question like “when was the last time we observed individual A”, or “how many times has individual A been involved in an interaction with individual B”, one must combine the results of querying all the behavioral tables. This is simple when there’s only two types of data we’re collecting, aggressions and groomings, but becomes increasingly complicated as more categories of data are collected.

To solve this problem SokweDB structures the database around events. Events are times when data is collected. An event may record a behavior, an individual’s location, or even be something like a record of what was recorded in ad-hoc textual notes. Events are categorized by behavior. A grooming event has its own behavior, an aggression event has it’s own behavior, and so forth.

Because an event can be one of so many different things, an event’s behavior code does not necessarily correspond with a behavior that a chimpanzee might exhibit. But the terminology works well enough. Queries specify an event’s behavior to report on a specific chimpanzee behavior.

Events are organized into watches, a period of time when an observer was observing – observing something.

Very many watches are follows, so all the behaviors recorded during a single follow of a focal are events that are related to a single watch.

Most other watches are records of an observation of a chimpanzee at the feeding station. In these cases there’s no actual follow, but the individual observed “takes the place” of the focal.

And some watches are just ad-hoc observations. For example, a pantgrunt observed by happenstance. In these cases a watch may be related to only one event.

Watches are categorized by type. These map to things like follows, feeding station observations, and the recording of chimpanzee locations. Queries specify a watch’s type to report on a specific data collection protocol, like a follow.

Individual chimpanzees are associated with events. Each individual plays a role in the event. Like a role in a theater play, where an actor may play a different role in different performances, chimpanzees play different roles in different events. Any event can be associated with multiple roles, each played by a different chimpanzee.

Each role, associated with some specific event, designates both a chimpanzee and, by way of a role code, the role the chimpanzee plays in the event.

Some roles are “special”, the system knows they have special meaning.

Most (if not all) roles associated with dyadic events, pairwise events like groomings or aggressions, have special meaning.

In the case of groomings and aggressions we only mention here two of the special role codes, although there are others. These are the special role codes Actor and Actee. The individual aggressing and the individual performing the grooming have the Actor role. The individual receiving the aggression and the individual receiving the grooming have the Actee role.

By making these two role codes “generic” and using them in both the case of grooming and the case of aggression, instead of having four codes, “groomer” and “groomee” and “aggressor” and “aggressee”, we can more easily query across behaviors. It becomes simple to ask questions, irrespective of behavior, about those receiving behaviors and those performing them.

The structure of the tables that hold the SokweDB support are very flexible in the kinds of data they may hold. It takes little in the way of modification to record hitherto unrecorded behaviors or to use new data collection protocols. And the real benefit is the relative ease of writing queries that return cross-behavioral or cross protocol information.

The cost is an increase in the complexity of writing a query that returns information on a single behavioral category, collected using a single protocol, like groomings involving the focal collected during a follow.

But views, which are relatively easy to create and maintain, can entirely remove the barrier to querying on a single behavioral category.[1] They do this by appearing to be a table, that looks just like a table that holds data on a single behavioral category.

The PANTGRUNTS_VIEW view is a good example of this.[2] It combines the all the underlying data kept in separate tables, and, in the case of roles, data that’s kept in separate rows – data that’s specifically pantgrunt related like whether there were multiple pantgrunt recipients, with the chimpanzees that play the roles of pantgrunter and pantgrunt receiver, with the time of the event, and with the date and focal (if the watch is a follow) of the watch –, into what looks and acts just like a regular table.

Data Retrieval Views

The views appearing in this section exist for convenience in querying. Some exist to make the data look more like the “old” data, as it appeared in the old MS Access database. Others reproduce common query patterns, eliminating the need to connect (join) multiple tables.

The columns of the OBS view

View	One row for each	Purpose	Tables/Views used
BIOGRAPHY	BIOGRAPHY_DATA row	Reproduce “old” data data	BIOGRAPHY_DATA
BREC_NOTES_VIEW	BRECORD_NOTES row	Simplify queries that join BRECORD_NOTES with other EVENTS related tables	OBS, BRECORD_NOTES

• BIOGRAPHY
  • Definition
  • Columns of the BIOGRAPHY View
  • Operations Allowed
• BREC_NOTES_VIEW (BRECord NOTES VIEW)
  • Definition
  • ER Diagram
  • Columns of the BREC_NOTES_VIEW View
  • Operations Allowed

Data Modification Views

The views appearing in this section exist so that data can be more easily uploaded into the database and more easily modified. These views may also be suitable for querying.

Some exist to make the data look more like the “old” data, as it appeared in the old MS Access database. Others reproduce common query patterns, eliminating the need to connect (join) multiple tables.

View	One row for each	Purpose	Tables/Views used
DYADS	EVENTS row, where the behavior observed is a dyadic interaction	Simplifies querying – puts the two individuals involved in a dyadic interactions into a single row.	WATCHES, EVENTS, ROLES (twice)
OBS (Observations)	EVENTS row, so one row for every observed event	Simplifies querying – attaches a date and a AnimID, etc., to each event.	WATCHES, EVENTS
PANTGRUNTS_VIEW	EVENTS row related to a PANTGRUNTS row	Upload pantgrunt data, reproduce “old” data	WATCHES, EVENTS, ROLES, PANTGRUNTS

• OBS (OBservationS)
  • Definition
  • ER Diagram
  • Columns of the OBS View
  • Operations Allowed
• DYADS
  • Definition
  • ER Diagram
  • Columns of the DYADS View
  • Operations Allowed
• PANTGRUNTS_VIEW
  • Definition
  • ER Diagram
  • Columns of the PANTGRUNTS_VIEW View
  • Operations Allowed

Footnotes

[1]

While it is relatively easy to write a query, or create a view, that combines the more generic tables containing watches, events, roles, and so forth, and then selects from the result a specific behavior, it is much more tedious and error prone to do the reverse.

Writing queries that, in effect, concatenate data that appears in different forms in different tables, each of which are specialized to hold only a very specific behavior, can get complicated quickly.

It is sometimes useful to be able to easily ask questions that aggregate across behaviors. SokweDB is designed so that it is not overly hard to ask complicated questions, and views make asking simple questions easy too.

[2]

At the time of this writing there are not views for every behavior. Should more views be needed to support queries on other behaviors, the code behind the PANTGRUNTS_VIEW view serves as an example that can be easily adapted.

Page last generated: 2026-08-13 23:02:22 UTC