Purpose and Intent:
The purpose of this role is to create a scalable and efficient database that can store large volumes of logistics data from PUB LOG, which includes National Stock Numbers (NSNs), CAGE codes, part numbers, contractor details, and other cataloging information. The developer should ensure the database supports fast querying, particularly for the specific queries related to material composition, weight, SAM status, and contractor data.
Goals:
- Build a robust database capable of storing millions of NSNs and related attributes.
- Ensure the database structure is optimized for the kinds of queries that will be made, including filtering by characteristics, material, and contractor information.
Detailed Scope of Work:
Database Schema Design:
- Design a schema that accommodates the following key fields:
- NSN, Part Numbers, CAGE Codes, SAM Status, Expiration Date, and Weight.
- Characteristics such as material composition (e.g., metal, plastic), criticality codes, and unit price.
- Include necessary fields for filtering based on SAM status (e.g., expired within a specific period), contractor activity, and other item attributes.
Data Storage:
- Store downloaded PUB LOG data on a secure database infrastructure (e.g., PostgreSQL, MySQL).
- Allow for periodic data updates from PUB LOG, ensuring data integrity and consistency with each update.
Optimization:
- Implement indexing for faster querying, particularly for high-priority attributes like CAGE codes, SAM status, and NSN characteristics.
- Ensure the database can handle queries efficiently for large datasets (millions of records).
Deliverables:
- Fully implemented database schema with stored PUB LOG data.
- Detailed documentation outlining the database schema, fields, and how to query the data.
- Optimized queries for handling contractor data, NSN attributes, and filtering based on weight and material.
Timeline:
Estimated Completion: 2-3 weeks.