Products have their own relational structure separate from the main contract fields. A contract can have multiple products, each with yearly fees and user allocations.
Schema Overview
Products are organized across three related tables:
vendor_products (Product Catalog)
↓
vendor_products_details (Fees per Year)
↓
vendor_products_users (User Allocations)
vendor_products (Product Catalog)
The main product catalog table that stores products offered by each vendor.
| Field Name | Type | Description |
|---|
id | number | Unique identifier for the product |
name | string | Product name |
vendor_id | number | Foreign key to vendors table |
delivery_method_id | number | Foreign key to data_delivery_types table (optional) |
created_at | string | Timestamp when product was created |
updated_at | string | Timestamp when product was last updated |
- Belongs to:
vendors (via vendor_id)
- Belongs to:
data_delivery_types (via delivery_method_id)
- Has many:
vendor_products_details
- Has many:
vendor_products_users
vendor_products_details (Annual Fees)
Stores the actual fees charged per product, per year, per contract. This is where pricing information lives.
| Field Name | Type | Description |
|---|
id | number | Unique identifier for the detail record |
contract_id | number | Foreign key to contracts table |
product_id | number | Foreign key to vendor_products table |
year | number | Fiscal year for this fee |
fees | number | Annual fee amount for this product in this year (optional) |
n_users | number | Number of users for this product in this year (optional) |
user_id | string | User who created/modified this record (optional) |
created_at | string | Timestamp when record was created |
- Belongs to:
contracts (via contract_id)
- Belongs to:
vendor_products (via product_id)
Key Concept: Each product can have different fees for different years within the same contract, enabling multi-year pricing structures.
vendor_products_users (User Allocations)
Tracks user count information for products within contracts.
| Field Name | Type | Description |
|---|
id | number | Unique identifier for the user record |
contract_id | number | Foreign key to contracts table (optional) |
product_id | number | Foreign key to vendor_products table (optional) |
number_of_users | number | Number of users allocated (optional) |
created_at | string | Timestamp when record was created |
updated_at | string | Timestamp when record was last updated |
- Belongs to:
contracts (via contract_id)
- Belongs to:
vendor_products (via product_id)
Last Updated: 11/6/2025
