Some traps to avoid when identifying attributes for your entities:

When mapping out your attributes (like in the El Grito Example below), the most important thing is to make sure each attribute belongs to the entity it is actually describing. For example, say you want a timeline of officers’ job promotions with start- and end-dates that they served at each rank. Are those start- and end-dates attributes of the “Officer” entity? It might seem so, but in fact those dates are descriptors of the officer’s rank history and not the officer per se.   

Let’s look at what would happen if you assigned Rank Start- and End-dates to the “Officer” entity:

OFFICERS (BAD EXAMPLE)

Since an officer might have multiple ranks during their career, you would end up with duplicate entries for individual officers in your database. You want to avoid a situation like this.

Ideally, you should instead have two related tables in your database, for “Officer,” and “Officer Rank History,” like this:

OFFICERS

OFFICER RANK HISTORY

By having separate tables for “Officer” and “Officer Rank History,” like above, you can record information about an officer’s changes in rank over time in your database without creating duplicate Officer records.

Another important data modeling principle (for relational databases, less so for NoSQL ones) is, in a given record, there should only be one value (i.e. entry) per attribute.  Keyword- or tag-type attributes often involve multiple values. For example, a policing incident might often involve more than one type of misconduct or abuse that you will want to tag. Here are two common but not-so-good ways to handle multiple values for an attribute:

INCIDENT MISCONDUCT (BAD EXAMPLE 1: LISTING DIVISIBLE VALUES)

INCIDENT MISCONDUCT (BAD EXAMPLE 2: REPEATING ATTRIBUTES)

In both examples above, there are multiple Misconduct Types for each record. In the first example, the entries work as “free text” but can’t be sorted by each misconduct type effectively. In the second example, the attribute is repeated so that each misconduct is in its own column. This also creates a sorting problem — notice that Incidents 2 and 3 actually involve the same three types of misconduct, but were entered in a different order, so the columns don’t match up.  

The ideal way to handle situations where there can be multiple values for an attribute is to make the attribute its own entity, and make an additional associative entity to relate them. So for the example above, a more ideal structure for a relational database would be:

INCIDENTS

MISCONDUCT TYPES

MISCONDUCT TAGS

The “Misconduct Tags” table above only has one value per attribute, so it enables you to sort by either Incident or by Misconduct.