When your native data is normalized, each MongoDB collection is decompounded into a set of parent-child tables. Fields containing simple types are mapped as columns in a parent table, while complex types, such as subdocuments and arrays, are mapped as child tables. Child tables share a foreign key relationship with their parent table.

For example, the collection residents contains the array vehicles and fields in the address document (or object). The collection's JSON structure can be rendered as follows:

{"_id": "ajx363",
 "name": "Sydney Smith",
 "address": {"street": "101 Main Street", "city": "Raleigh", "state": "NC"},
 "county": "Wake",
 "vehicles": ["car", "boat"]
}

{"_id": "tzn525",
 "name": "Cora Welch",
 "address": {"street": "191 First Street", "city": "Chapel Hill", "state": "NC"},
 "county": "Orange",
 "vehicles": ["scooter", "truck"]
}

The collection has been decompounded into separate but related tables. The normalization of the residents collection yields one parent table and two child tables. In the parent table, the _id field is mapped as a primary key column, and fields with other simple types are mapped as relational columns. The parent table adopts the name RESIDENTS and takes the following form:

Table 1. RESIDENTS
_ID (PK) NAME COUNTY
ajx363 Sydney Smith Wake
tzn525 Cora Welch Orange

The information in the vehicles array is mapped to the following child table:

Table 2. RESIDENTS_VEHICLES
RESIDENTS_ID (FK) POSITION VEHICLES
ajx363 0 car
ajx363 1 boat
tzn525 0 scooter
tzn525 1 truck

The mapping of the vehicles array to the RESIDENTS_VEHICLES table is handled as follows:

  • The table name, RESIDENTS_VEHICLES, is derived from the name of the parent table and the array.
  • To establish a foreign key relationship with the parent table, a column is generated based on the _ID field using the following naming convention: 
    <parent_table>_ID

    For example, RESIDENTS_ID.

  • The primary key of the child table is a composite key formed by the primary key of the parent table (RESIDENTS_ID) combined with the positional information contained in the POSITION column.
  • Fields in the object are mapped to columns in the child table. For example, the vehicles field becomes the VEHICLES column.

The information in the address object maps to the following child table:

Table 3. RESIDENTS_ADDRESS
RESIDENTS_ID (PK & FK) STREET CITY STATE
ajx363 101 Main Street Raleigh NC
tzn525 191 First Street Chapel Hill NC
The mapping of the address object to the RESIDENTS ADDRESS table is handled as follows:
  • The table name, RESIDENTS_ADDRESS, is derived from the parent table and the name of the object.
  • To establish a foreign key relationship with the parent table, a column is generated based on the _ID field using the following naming convention: 
    <parent_table>_ID

    For example, RESIDENTS_ID.

  • Fields in the object are mapped to columns in the child table. For example, the city field becomes the CITY column.