Flight Fares: DynamoDB Single-Table Design Example

All single-table examples · Open interactive viewer

How this model is organized

Bi-directional model for fares. This reference model by Rick Houlihan contains 4 entity types and 3 index definitions. Use the keys and access patterns below to understand how its records fit together before exploring the interactive viewer.

Entities and key templates

Each entity defines the attributes stored on its items. Key templates show how attribute values are combined; when no template is defined, the key value comes from the item itself.

• fare: type, PK, SK, start, end, origin, dest, GSI1PK, GSI1SK, class, __id.
  • PK: Provided by the item
  • SK: ${dest}#${start}#${class}
  • GSI1PK: Provided by the item
  • GSI1SK: ${origin}#${start}
• flight: type, PK, SK, depart, arrive, origin, dest, GSI1PK, GSI1SK, isSegment, segId, class, number, GSI2PK, GSI2SK, __id.
  • PK: Provided by the item
  • SK: ${origin}#${depart}#${number}#${segId}
  • GSI1PK: Provided by the item
  • GSI1SK: ${origin}#${arrive}
  • GSI2PK: ${number}
  • GSI2SK: ${segId}
• assignment: type, PK, SK, SSR, flight, segId, seat, depart, arrive, origin, dest, GSI2PK, GSI2SK, __id, number.
  • PK: Provided by the item
  • SK: ${depart}#${flight}#${segId}#${seat}
  • GSI2PK: ${number}
  • GSI2SK: ${segId}#${seat}
• booking: type, PK, SK, flight, depart, arrive, segments, origin, dest, __id.
  • PK: Provided by the item
  • SK: ${depart}#${flight}

Index keys

• primary: partition key PK; sort key SK.
• GSI1: partition key GSI1PK; sort key GSI1SK.
• GSI2: partition key GSI2PK; sort key GSI2SK.

Example access patterns

These named queries are included in the model. Open the viewer to inspect their sample values and any filters.

• All scheduled flights from DEN in 2021: Query on primary (Begins with).
• Fares from DEN to JFK: Query on primary (Begins with).
• Passenger's assignments: Query on primary (Equal).
• Passenger's bookings in 2021: Query on primary (Begins with).
• Fares from JFK to SFO: Query on primary (Begins with).
• All details about flight by number: Query on GSI2 (Equal).
• Flights to SFO from JFK in 2021: Query on GSI1 (Begins with).
• All flights to SFO: Query on GSI1 (Begins with).
• All direct flights to SFO: Query on GSI1 (Begins with).
• All non-stop flights from DEN: Query on primary (Equal).

Explore and adapt the example

Download the model JSON to inspect the full schema and sample records. Compare its access patterns with your application's requirements before adapting the design. For a guided editing workflow, see the single-table design tool.