Member Avatar for atiqahali.nurul
atiqahali.nurul 0 Newbie Poster

An international courier, express and parcel Services Company is setting up a package shipping system for customers to ship packages. Whenever a customer wishes to ship a package, they can call the company’s customer service personnel to place a shipment request.
A computer system is needed to track the shipment details of the package. This will enable the company to keep track of their customer’s details, shipment details and to schedule a pick-up of the shipment.

When a customer calls in to place a request for a shipment, the customer’s details, the company’s details (where applicable), the type of shipment request and the estimated cost of shipment are logged into the system by the customer service personnel. The courier company provides express delivery of packages by air, road, rail or ocean and allows customers to track the movement of the package. Each mode of delivery has a variety of services such as:
• Day-specific, door-to-door delivery
• Fast delivery services
• Low cost delivery services
• Handling of dangerous, fragile and temperature sensitive packages
• Easy availability world wide
When the customer calls to place a request, the customer service personnel will provide several delivery options along with its cost. The system will maintain a log of the delivery options and its associated costs. Once the customer has decided on the delivery option, the shipment details are confirmed and the customer service personnel creates a log in the system if a pick-up service of the package is requested. The date, time and location to pick-up the package is also logged into the system in order for the logistics department to access the pick-up information.

Once the package has been picked-up from the customer or delivered to the company, a package tracking number is assigned to each package in order for customers to track the movement of the package. The handling team uses a tracking device to scan the barcode on the package at each location from the source to the intended destination of the package. Upon scanning the barcode, the shipment details are displayed and the handling team is able to update the system with the current location of the package and the condition of the package.
Once the package has been delivered to the requested destination, the recipient of the package will sign the delivery note to confirm receipt of the package. The recipient’s signature will be scanned into the system as proof to the customer of the receipt of the package. The delivery details will also be logged into the system

  • 1 Contributor
  • 0 Replies
  • 335 Views

Dani AI

Generated

As described, the system must capture customers/companies, selectable delivery services and their costs, pickup scheduling, per-package tracking scans and proof-of-delivery signatures. The ERD should separate shipments (a booking) from packages (one or more physical pieces) so each package can have its own tracking number and event history; delivery options must be captured as a selectable snapshot so historical cost/feature queries stay correct.

Core entities and cardinalities to model: CUSTOMER (person or company), ADDRESS (reusable), SHIPMENT (booking) 1--* PACKAGE (pieces, unique tracking_number), SERVICE_OPTION (air/road/rail/ocean + features), SHIPMENT_SERVICE (snapshot of chosen option and price), PICKUP_REQUEST, TRACKING_EVENT (append-only scan history), LOCATION and DEVICE, and DELIVERY_SIGNATURE (store reference or BLOB). Important relations: one CUSTOMER -> many SHIPMENTS; one SHIPMENT -> many PACKAGES; one PACKAGE -> many TRACKING_EVENT rows.

Modeling and operational notes: use TIMESTAMP WITH TIME ZONE for scan times; keep tracking_event append-only (no destructive updates) to preserve audit; snapshot price and service details on the purchase row so later rate-table changes do not retroactively alter historical shipments; enforce UNIQUE(tracking_number) and index tracking_event by package_id,event_time (descending). For signature images prefer object storage and store only a stable URI and hash in the DB (store BLOB only if policy requires). Capture hazardous-goods metadata (UN number, DG class) as required columns and validate business rules before shipment acceptance. For high-volume tracking, partition tracking_event by event_date and batch-insert from devices with idempotency tokens to prevent duplicate scans.

Example Oracle-style table skeleton (abbreviated):

CREATE TABLE shipment (
  shipment_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  ship_date DATE,
  origin_address_id NUMBER,
  dest_address_id NUMBER,
  estimated_cost NUMBER(12,2)
);

CREATE TABLE package (
  package_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  shipment_id NUMBER NOT NULL,
  tracking_number VARCHAR2(64) UNIQUE,
  weight NUMBER(8,3),
  length NUMBER(8,2),
  width NUMBER(8,2),
  height NUMBER(8,2)
);

CREATE TABLE tracking_event (
  event_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  package_id NUMBER NOT NULL,
  event_time TIMESTAMP WITH TIME ZONE,
  location_code VARCHAR2(100),
  event_code VARCHAR2(50),
  details VARCHAR2(2000)
);

Common pitfalls: not snapshotting prices (loss of historical accuracy), storing large binaries in DB without lifecycle/backup plans, and missing indexes on hot query paths (tracking_number, package_id,event_time).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.