1.1. Brainstorming Possible Entities

The first step in designing a data model is making a list of entities, or what the database will describe. It can be useful to start with a big list and refine from there.

Entities from Questions

As previously discussed, you can start by looking at the goals of the project and what the database users want to know about. What types of persons, organizations, objects, or events are the questions about?

Berkeley Copwatch Example

Using the questions we developed with Berkeley Copwatch, we started creating a list of potential entities for their new data model. Looking at the questions and underlining the nouns (i.e. the persons, organizations, objects, or events) is a good place to start. For example:

• Which videos show which incidents involving which officers?
• Which officers most often use force?
• What kind of force are officers using?
• What are the histories of incidents for each officer?
• Who else was present at an incident? What did they do?
• Are officers deliberately interfering with the right to watch? Which officers?

This gives us a rough idea of what or who the questions are about. From there, we could start formulating a list of possible entities:

Entities Emerging from Reviewing Content 

Another way to identify possible entities for your data model is to start digging through your materials. Just start gathering data to see what information is available, and what information is important for your project, then sort the data into the “things” they seem to be describing. For example, the logging notes for a video of an arrest might include an officer’s badge number, the location of the incident, and the name of the person arrested, etc. You might also notice that, in your collection, there are multiple videos of the same arrest, and that you will need to distinguish which parts of the incident are captured in which video. From this, some possible entities for your data model can already be identified:

El Grito Example

For the El Grito project, we reviewed all the videos and made notes in a spreadsheet, creating columns as we went along, and as we uncovered new types of information that were important to capture. 

Here is a rough list of “things,” or entities, that seemed to be described in the metadata we were collecting:

1.2. Refining the Entity List

Once you have a big list of possible entities, the next step is to refine the list. Some entities might not need to be included if there isn’t data that you want to track about them, or some entities might actually work better as attributes of another entity. Some entities on the list might be very similar things, and it would make sense to collapse them into a single entity. There might be new entities you need to add to the list.

To refine your entity list, ask yourself:

• Does this entity need to be included? Are there other entities that we need to add? Is there going to be data about this thing that we want to track that serves the purpose of this project?
• Is this entity similar to other entities on the list? Can they be consolidated?
• Do I want to align my entities with an external standard or someone else’s database? (NB: They don’t have to be exactly the same, as long as the structures can be mapped to one another in some way.)

There are no hard and fast rules about what should be an entity. As a rule-of-thumb, the simpler the better: focus on the goals of the database, and don’t create entities you don’t need, or else you will be creating a lot of extra work for data entry down the line. As you review and test, you might need to make revisions to make your model work best for your needs.

El Grito Example

On the big preliminary list above, we have “Police Department” and “Police Precinct” as possible entities. But in our project, the police department is always going to be “NYPD” and there isn’t data about the department per se that we want to track. Meanwhile, we do want to keep track of data about police precincts, like which officers belonged to which precinct and when. So it would make sense to keep “Police Precinct” as an entity, and just make “Police Department” an attribute of “Police Precinct.”

While researching officers, we learned that officers do not have permanent shield numbers, and that they can change over time. So we needed to create an additional entity for Shield History in order to track the start and end dates of shield numbers for each officer. We also learned that officers were often receiving promotions and salary increases over time, despite their involvement in numerous incidents and alleged misconduct. This is information that is important to track, so we also needed to create entities for Rank History and Salary History in order to track when those promotions took place. (Shield History, Rank History, and Salary History are considered weak entities because they’re dependent on the Officers entity to have meaning. If it’s confusing as to why they need to be separate entities instead of just attributes of the Officers entity, the Identifying Attributes section goes into more detail.)

Examining our list further, “Victims,” “Witnesses,” “Filmers,” and “People who have Additional Information about Incident” could really all be collectively called “People” instead of having separate entities for each of type of person. Whether they are victims, witnesses, or filmers could be designated using “Role”  as an attribute for a person in relation to a specific incident. This especially makes sense because a single person might be a victim in one incident, a witness in another, and a filmer in yet another, and we want all of these instances related to the same Person, not as a separate Victim, Witness, or Filmer.