While the positions and education tables in Figure 3-1 are examples of one-to-many or one-to-few relationships (one résumé has several positions, but each position belongs only to one résumé), the region_id field is an example of a many-to-one relationship (many people live in the same region, but we assume that each person lives in only one region at any one time).
If we introduce entities for organizations and schools and reference them by ID from the résumé, then we also have many-to-many relationships (one person may have worked for several organizations, and an organization has several past or present employees). In a relational model, such a relationship is usually represented as an associative table, or join table, as shown in Figure 3-3: each position associates one user ID with one organization ID.
Diagram illustrating a many-to-many relationship in a relational database, showing how user IDs, organization IDs, and job positions are associated across users, positions, and organizations tables.
Figure 3-3. Many-to-many relationships in the relational model
Many-to-one and many-to-many relationships do not easily fit within one self-contained JSON document; they lend themselves more to a normalized representation. In a document model, one possible representation is given in Example 3-2 and illustrated in Figure 3-4. The data within each dotted rectangle can be grouped into one document, but the links to organizations and schools are best represented as references to other documents.
Example 3-2. A résumé that references organizations by ID
{
"user_id": 251,
"first_name": "Barack",
"last_name": "Obama",
"positions": [
{"start": 2009, "end": 2017, "job_title": "President", "org_id": 513},
{"start": 2005, "end": 2008, "job_title": "US Senator (D-IL)", "org_id": 514}
],
...
}
Many-to-many relationships often need to be queried in “both directions”—for example, finding all the organizations that a particular person has worked for, and finding all the people who have worked at a particular organization. One way of enabling such queries is to store ID references on both sides, such that a résumé includes the ID of each organization where the person has worked, and the organization document includes the IDs of the résumés that mention that organization. This representation is denormalized, since the relationship is stored in two places, which could become inconsistent with each other.
Diagram illustrating many-to-many relationships in a document model, showing connections between users, jobs, organizations, and educational institutions.
Figure 3-4. Many-to-many relationships in the document model—the data within each dotted box can be grouped into one document
A normalized representation stores the relationship in only one place and relies on secondary indexes to allow the relationship to be efficiently queried in both directions. In the relational schema shown in Figure 3-3, we would tell the database to create indexes on both the user_id and org_id columns of the positions table.
In the document model of Example 3-2, the database needs to index the org_id field of objects inside the positions array. Many document databases and relational databases with JSON support are able to create such indexes on values inside a document.