Skip to main content

​
Introduction to the Part_Numbers Table

The part_numbers table is designed to store and manage information about specific part numbers within the Serial application. This table plays a crucial role in the system by linking part numbers to companies and components, while also tracking their status and associated metadata.

​
Table Structure

The part_numbers table is structured to provide comprehensive information about each part number. Here’s a detailed breakdown of its columns:
Column NameData TypeConstraintsDescription
iduuidprimary keyUnique identifier for each part number entry
company_iduuidforeign key, not nullReference to the company associated with this part
pntextnot nullThe actual part number identifier
is_activebooleannot nullIndicates whether the part number is currently active
created_attimestamp with time zonenot nullTimestamp of when the entry was created
last_edited_attimestamp with time zonenot nullTimestamp of the last edit to this entry
last_edited_user_idtextforeign key, nullableReference to the user who last edited this entry
descriptiontextnullableOptional description of the part number
component_iduuidforeign key, nullableReference to the associated component
metadatajsonbnullableCustom metadata stored in JSON format

​
Usage and Functionality

The part_numbers table is designed to be a central repository for part number information within the Serial application. Here are some key points about its usage:
  1. Company and Component Association: Each part number is linked to a specific company and can be associated with a component, allowing for organized tracking of parts across the system.
  2. Status Tracking: The is_active field enables easy filtering of current and discontinued part numbers, facilitating inventory management and historical record-keeping.
  3. Flexible Metadata Storage: The metadata column, using JSONB format, allows for storing additional custom information about part numbers without requiring schema changes.
  4. Edit History: By tracking creation and last edit times, as well as the user who made the last edit, the table provides an audit trail for part number modifications.

​
Notes

  • The part_numbers table is referenced by the unique_identifiers table, indicating its role in tracking individual instances or units of parts.
  • Example of querying active part numbers for a specific component: 
    SELECT pn, description, metadata
FROM part_numbers
WHERE component_id = '324da2e3-30c4-4512-a432-8593a52b2019'
  AND is_active = true;
  • The table is used in various database functions, such as part_numbers_propagate_new_data, which suggests that changes to part numbers may trigger updates in related tables.
By leveraging the part_numbers table, the Serial application provides a robust system for managing and tracking part numbers across different companies and components, enabling efficient inventory management and traceability throughout the manufacturing or assembly process.