Designing a Database Schema for Scrap Metal Price History

If you are building an application that tracks scrap metal prices over time, your database design will determine how well it scales and how fast your queries run. Pricing data accumulates quickly: 14 grades with daily prices generates over 5,000 rows per year. Add intraday updates and multiple regions, and you are looking at tens of thousands of rows annually. This guide walks through a proven schema design for historical scrap metal pricing.

Core Tables

Start with three tables: metals, grades, and prices. The metals table stores the metal categories (copper, aluminum, steel, brass) with an ID and name. The grades table stores individual scrap grades with a foreign key to metals, the grade name, ISRI code, and the standard pricing unit (per pound or per gross ton).

The prices table is the heart of the system. Each row represents a single price observation. Columns include the grade ID (foreign key), price as a decimal, the observation date, source identifier, and region. Use a composite primary key or unique constraint on grade ID plus date plus source to prevent duplicate entries.

Choosing Data Types

Store prices as DECIMAL(10, 4) or equivalent fixed-precision type. Floating-point types like FLOAT introduce rounding errors that accumulate in aggregate calculations. Four decimal places handles all scrap metal pricing precision requirements since copper might be $3.4575 per pound.

Use DATE for the observation date rather than TIMESTAMP if you are storing daily closing prices. If you need intraday granularity, use TIMESTAMP WITH TIME ZONE and always store in UTC.

Indexing Strategy

The most common query patterns for price history are fetching all prices for a specific grade within a date range and comparing prices across grades for a specific date. Create a composite index on (grade_id, date) to optimize both patterns. This index supports range scans on date within a grade and point lookups for a specific grade and date combination.

If you frequently query by metal category rather than specific grade, add an index on the metals table or use a materialized view that joins metals, grades, and prices.

Partitioning for Scale

If your dataset grows beyond a few hundred thousand rows, consider partitioning the prices table by date range. Monthly or yearly partitions keep each partition small enough for fast scans while making it easy to archive or drop old data. PostgreSQL and MySQL both support range partitioning natively.

Loading Data from the API

Build a data loader that calls the ScrapMetal API's historical endpoint, maps the JSON response to your schema, and performs an upsert (insert or update on conflict). Upsert logic handles the case where you re-fetch a day's data and the price has been corrected by the source.

Run the loader on a daily schedule. The ScrapMetal API returns data for the current day by default when no date range is specified, making the daily load a single API call.

Query Patterns

Common queries include retrieving the latest price for each grade (a distinct-on or window function query), calculating moving averages for trend analysis, and computing period-over-period changes for performance dashboards. With the indexing strategy described above, all of these queries perform well even on several years of data.