logo
ExamplesEditorPricingDocs
37.1kSign in

Parcel tracking and courier network schema

A courier network schema built on an append-only scan log: shipments and the packages inside them, hubs and planned routes, scan events as the only truth, derived status, delivery attempts, exceptions, proof of delivery, and promised versus actual delivery.

Parcel Tracking ER diagram

Parcel tracking and courier network schema n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 customers idBIGINT account_numberVARCHAR company_nameVARCHAR contact_emailVARCHAR contact_phoneVARCHAR billing_cityVARCHAR billing_country_c…CHAR credit_terms_daysSMALLINT is_activeBOOLEAN created_atTIMESTAMPTZ service_levels idBIGINT codeVARCHAR nameVARCHAR transit_daysSMALLINT guaranteed_by_timeTIME signature_requiredBOOLEAN includes_insuranc…INTEGER is_internationalBOOLEAN is_activeBOOLEAN hubs idBIGINT codeVARCHAR nameVARCHAR kindVARCHAR address_line1VARCHAR cityVARCHAR country_codeCHAR timezoneVARCHAR latitudeNUMERIC longitudeNUMERIC sort_cutoffTIME is_activeBOOLEAN routes idBIGINT codeVARCHAR origin_hub_idBIGINT destination_hub_idBIGINT transport_modeVARCHAR scheduled_departu…TIME transit_minutesINTEGER operating_daysVARCHAR is_activeBOOLEAN shipments idBIGINT tracking_numberVARCHAR customer_idBIGINT service_level_idBIGINT origin_hub_idBIGINT destination_hub_idBIGINT sender_nameVARCHAR sender_address_li…VARCHAR sender_cityVARCHAR sender_postcodeVARCHAR sender_country_co…CHAR recipient_nameVARCHAR recipient_address…VARCHAR recipient_cityVARCHAR recipient_postcodeVARCHAR recipient_country…CHAR recipient_emailVARCHAR recipient_phoneVARCHAR declared_value_mi…INTEGER currency_codeCHAR package_countSMALLINT promised_delivery…TIMESTAMPTZ estimated_deliver…TIMESTAMPTZ delivered_atTIMESTAMPTZ created_atTIMESTAMPTZ packages idBIGINT shipment_idBIGINT barcodeVARCHAR piece_sequenceSMALLINT weight_gramsINTEGER length_mmINTEGER width_mmINTEGER height_mmINTEGER contents_descript…VARCHAR is_dangerous_goodsBOOLEAN current_statusVARCHAR current_hub_idBIGINT last_scan_idBIGINT status_updated_atTIMESTAMPTZ shipment_legs idBIGINT shipment_idBIGINT leg_sequenceSMALLINT route_idBIGINT origin_hub_idBIGINT destination_hub_idBIGINT planned_departure…TIMESTAMPTZ planned_arrival_atTIMESTAMPTZ actual_departure_…TIMESTAMPTZ actual_arrival_atTIMESTAMPTZ statusVARCHAR scan_events idBIGINT package_idBIGINT shipment_idBIGINT hub_idBIGINT courier_idBIGINT event_codeVARCHAR occurred_atTIMESTAMPTZ recorded_atTIMESTAMPTZ sourceVARCHAR latitudeNUMERIC longitudeNUMERIC notesVARCHAR couriers idBIGINT employee_codeVARCHAR full_nameVARCHAR home_hub_idBIGINT phoneVARCHAR vehicle_typeVARCHAR is_activeBOOLEAN delivery_attempts idBIGINT package_idBIGINT courier_idBIGINT scan_event_idBIGINT attempt_numberSMALLINT attempted_atTIMESTAMPTZ outcomeVARCHAR latitudeNUMERIC longitudeNUMERIC note_leftBOOLEAN next_attempt_onDATE exceptions idBIGINT package_idBIGINT scan_event_idBIGINT kindVARCHAR severityVARCHAR opened_atTIMESTAMPTZ opened_byVARCHAR detailVARCHAR resolved_atTIMESTAMPTZ resolutionVARCHAR proofs_of_delivery idBIGINT package_idBIGINT delivery_attempt_…BIGINT courier_idBIGINT delivered_atTIMESTAMPTZ received_by_nameVARCHAR relationshipVARCHAR signature_image_k…VARCHAR photo_image_keyVARCHAR latitudeNUMERIC longitudeNUMERIC

Related database schemas

PostgreSQL
Logistics

Last-mile delivery & route planning database schema

Delivery operations modelled properly: depots, drivers and shifts, vehicle capacity constraints, orders with time windows, routes planned per day, stops in planned and actual sequence, geofenced arrivals, failed attempts, proof of delivery and re-sequencing history.

PostgreSQL
Logistics

Vehicle fleet management database schema

How a vehicle fleet is modelled: vehicles and drivers, licence entitlements with expiry, assignment history, downsampled telematics and fault codes, maintenance due by mileage or date, work orders and parts, fuel, inspections and incidents.

SQL Server
Logistics

International freight forwarding database schema

A SQL Server schema for international freight: bookings and incoterms, shipments and container consolidation, multi-leg sea, air and road journeys, carrier rate contracts, customs declarations, milestone events, documents, charges and invoicing.

PostgreSQL
Logistics

Returns & RMA (reverse logistics) database schema

How returns are actually modelled: RMA authorisations and eligibility windows, versioned policies, return reasons, inbound inspection and grading, dispositions from restock to scrap, refunds against exchanges and store credit, labels, restocking fees and return-abuse signals.