Database Schema Design: A Practical Guide for Scraped Data

Your scraper is returning product records, but yesterday's price has already been overwritten. A retailer has changed its SKU format, an unexpected specifications object has appeared, and the analyst building an AI query assistant wants access to the same tables. The extraction works. The storage contract doesn't.

Database schema design is where you decide what a record means, which facts must remain stable, and what you'll preserve when a source changes. For scraped data, a useful design separates source evidence from business entities, protects history, and gives applications and AI consumers a deliberate interface rather than unrestricted access to whatever arrived.

Six Stages from Requirements to a Shipped Schema

Developers often jump straight to table definitions because the scraper's JSON already looks like a schema. They also skip workload definition, then discover that the dashboard needs historical comparisons while the database stores only current values.

Establish meaning before optimizing storage

Start with business rules. A price belongs to an observation, not permanently to a product; a product title describes the retailer's listing, not necessarily a universal item shared across merchants.

Normalize those facts into tables, then write the rules the database must enforce. A uniqueness constraint on (retailer_id, sku) expresses product identity within a retailer, while foreign keys prevent observations from referencing nonexistent products.

Constraints need explicit failure behavior. Decide whether an invalid record should fail the ingestion batch, enter quarantine, or be retried after its parent record arrives.

Turn expected use into a benchmark

Write representative queries before committing to the normalized table list, then formalize their volumes and frequencies during workload design. Include latest-price lookups, daily price comparisons, company-level lead exports, and ingestion bursts rather than testing only convenient point queries.

The collection schedule matters too. Designing a real-time data pipeline requires distinguishing when a page was fetched, when its record was processed, and when downstream users expect it to become available.

Load representative data and compare equivalent queries against normalized, indexed, and selectively denormalized designs. Include concurrent ingestion so a fast reporting query doesn't conceal unacceptable write contention.

Keep duplicated attributes only when the measured benefit pays for reconciliation, backfills, and ownership.

Readiness test: If you can't name the queries the schema must serve, you're not ready to normalize the model into its final table list.

How to Turn Scraping Requirements into Entities and Keys

1. List the questions the data must answer. Ask what a SKU cost at a particular capture time, which retailer offered it, and which source page supplied the value. For a product monitor, distinguish the displayed price from an inferred discount or converted amount.

2. Pick the grain of each table. Write a sentence beginning with “Each row represents” for every proposed table. A product row represents a retailer listing; a price observation represents a captured offer at a particular store and time, potentially including variant or membership conditions.

3. Name entities from repeated business subjects. Retailers, products, observations, categories, and source pages deserve separate consideration because they have different identities and lifecycles. Don't create a table for every JSON object, a product's shipping details might be attributes of its captured offer.

4. Mark relationships before choosing column types. A product can have many observations, while products and categories usually require a many-to-many relationship through product_category. If a category assignment is itself scraped and disputed, give that relationship provenance rather than treating it as an unquestionable classification.

5. Choose stable keys and protect external identity. Use a surrogate bigint primary key for a retailer listing and retain UNIQUE (retailer_id, sku) as its business constraint. If a retailer changes an identifier, reconcile the listing through an explicit mapping instead of treating every changed SKU as the same product.

6. Connect evidence to the rows it supports. Add foreign keys and a source_id to scraped observations so a product price can be traced to its capture. When a canonical product combines several pages, use a provenance relationship rather than pretending a single source explains every attribute.

Separate business identity from page structure

The relational model's useful foundation is data independence, separating logical relations from physical storage, as described in the Springer chapter on relational databases. Apply that separation to scraping: a CSS selector, API path, or page layout shouldn't determine your product's permanent identity.

A URL alone is also insufficient evidence. The same URL can return a different price, location, or variant later, so connect observations to a particular capture, not merely to a page address.

Workflow-heavy datasets need equally explicit boundaries. For example, erecruit's discussion of tracking candidate stages and clearance details is useful context when distinguishing a person's identity from changing workflow facts; a product's identity and changing availability deserve the same separation.

Finish with a plain-language review. Each table should have a coherent purpose, and every scraped observation should lead back to the capture that supports it.

Normal Forms, Data Types and Indexes That Earn Their Cost

Normalization prevents separate copies of the same fact from drifting apart. Apply it to actual dependencies, not to a diagram's appearance.

• First normal form: Don't pack queryable categories into a comma-separated string. Represent category membership through a junction table.
• Second normal form: If an observation has a composite business key, a product title depends on the product, not the complete observation key. Keep that title with the product unless you intentionally need its captured historical value.
• Third normal form: A store's city depends on the store, not on each price observation. Store it with the store.
• Boyce–Codd normal form: Check whether every determinant is a candidate key. A store code determining city inside a broader relation signals a dependency worth separating.
• Fourth normal form: Independent image and tag collections shouldn't generate every image-tag combination in a shared table. Separate those relationships.
• Fifth normal form: Investigate complex join dependencies only when the business rules require them. Scraped catalogs rarely benefit from decomposition nobody can explain or maintain.

Let equivalent workloads settle the argument

In an IMDb-derived benchmark using seven SQL queries, moving from first to second normal form reduced disk size by 10.3%, while read-only throughput with 48 concurrent clients increased by 311%, according to the normalization benchmark paper. Those results don't predict your scraper's performance, but they do reject the assumption that decomposition necessarily makes reads slower.

Across 1,350 executions, a controlled PostgreSQL 15 study found that aggressive full denormalization made median reads 1.95 to 2.91 times slower at small scale factors and increased block reads 6.8 to 8.3 times, as reported in the PostgreSQL normalization and denormalization benchmark. Wider rows reduced page density, making supposedly simpler reads more expensive.

Compare identical query semantics, data distributions, cache conditions, and concurrency. Fewer tables don't automatically mean faster queries, and denormalizing before measuring row width, cache behavior, and write amplification hides the cost.

Choose types and access paths deliberately

Money needs explicit precision. The decimal type shown is a practical starting point for ordinary retail prices, not a universal monetary format. Preserve the source representation when fractional pricing or currency-specific precision requires more detail.

For timestamps, distinguish an instant from its presentation. PostgreSQL timestamptz doesn't preserve the original named time zone, so retain the source zone separately when local trading hours matter.

The identity constraint should match the retailer's actual SKU scope. If SKUs are unique only within a marketplace seller, include that seller in the business key.

Order composite indexes around query predicates. The product-leading history index suits product-specific ranges; it isn't a substitute for every time-based reporting access path.

Don't convert raw HTML into parsed JSON and call it exact preservation. Keep original bytes when replay or evidence requirements demand them, and avoid creating redundant foreign-key indexes when an existing index already has the needed leading columns.

Partitioning also adds routing and maintenance work. Add it when representative plans demonstrate useful pruning, not because the table is called history.

Stop decomposing when the next form removes no meaningful anomaly in your workload. Document every intentional exception in schema comments.

Relational and Document Schemas for Scraping Use Cases

The important difference isn't whether the input happens to be JSON. It's which boundaries you need for reads, updates, retention, and integrity.

Product catalogs and price histories

For a product catalog, decide whether a variant has an independent identity. A size or color that carries its own SKU, availability, and price generally deserves a separately addressable record, even if the retailer nests it inside a parent product.

A document works well when the application fetches a complete listing with its images and variants. Relational tables become more useful when analysts compare category membership, reconcile retailers, or enforce shared references across listings.

Price history needs a different boundary: an observation is an event. Append a new observation instead of updating an old amount, and include the offer conditions needed to distinguish store, variant, delivery region, or membership pricing.

An embedded, capped observation array is convenient for a recent-price widget, but it's not a complete historical archive. Once you need independent retention or broad time-range analysis, use a separate observation collection or relational history table.

Define retry behavior too. An ingestion identifier should prevent a replayed capture from becoming a duplicate event without suppressing genuinely separate observations that happen to share a timestamp.

Lead enrichment and field ownership

Lead enrichment needs attribution at the field level. A company domain from one source and a job title from another shouldn't lose their provenance when the lead is merged.

Keep enrichment runs independently addressable, and associate each extracted field with its producing run. Materialize a current lead profile using explicit precedence rules, such as preferring a verified company source over an older directory entry.

The flexible lead_fields approach costs you convenient typing and simple queries. Promote frequently filtered, well-defined fields into typed canonical columns while retaining the run-level evidence behind them.

Transport format is a separate decision. The practical distinctions in CSV versus JSON for web scraping help with interchange, but neither format determines your storage model.

Choose documents when source-specific records vary and are usually read whole. Choose relational tables when cross-record joins and database-enforced relationships drive the application.

Handling Unstable Scraped Data and Schema Changes

“Design for flexibility” isn't a migration strategy. Schema guides often emphasize keys and normalization while leaving application compatibility, historical interpretation, and AI consumers unresolved; the schema-evolution study examines how changed structures can undermine text-to-SQL reliability.

Neither freezing everything nor accepting every new field automatically solves that problem. Define where change is allowed and where a reviewed contract begins.

Keep the ingestion boundary visible:

Build layers with different responsibilities

• Raw landing: Store the payload with source_url, fetched_at, and parser_version. Apply access and retention controls to this evidence.
• Canonical entities: Map accepted fields into typed business structures using explicit conversion rules.
• Quarantine: Retain malformed records and unexpected fields with a reason and responsible owner. An unfamiliar key mustn't trigger an automatic production column.
• Change history: Record accepted changes append-only, with schema-version metadata that explains how to interpret them.

A missing price is not necessarily a deleted offer. Keep missing, invalid, unavailable, and explicitly removed states distinguishable so parser failures don't masquerade as business events.

Classify migrations and consumer exposure

Nullable additions are often compatible, but only if consumers tolerate additional fields. Renames, splits, merges, and changed units need versioned contracts or compatibility views with a defined transition window.

Before migration, list dashboards, exports, scheduled jobs, and model-facing queries. Record which field each consumer reads and test its expected behavior, rather than estimating impact from the number of changed columns.

For enrichment exports, Pipecorn's overview of B2B prospect list tools provides useful context for identifying downstream list-building consumers. Treat exported field names and meanings as contracts too.

Use this walkthrough alongside a migration review:

AI access needs its own projection. The text-to-SQL schema-leakage paper identifies exposure risks and recommends restricting which schema components a system reveals.

Give models curated views, stable business aliases, and least-privilege database roles. Exclude sensitive internal names from model-readable documentation, audit generated queries, and treat scraped text as untrusted content rather than instructions.

Design judgment: When pages change unpredictably, preserving source fidelity can be more valuable than adapting automatically. A replayable capture lets you repair interpretation without accepting an accidental business meaning.

Test and Migrate Your Schema Before Launch

Run this checklist before the first production write. Record the command, result, and owner for each check so approval rests on reproducible evidence.

Exercise reads, writes, and recovery

1. Run the workload against realistic seeded data. Include uneven retailer sizes, missing fields, long histories, and ingestion happening alongside reads. Empty tables won't reveal the expensive access paths.

2. Inspect execution plans. Use PostgreSQL EXPLAIN (ANALYZE, BUFFERS) on safe test workloads to examine row estimates, scans, and index use. Don't force an index merely because it exists; a sequential scan can be appropriate for a broad query.

3. Rehearse migration and recovery. Run forward and backward migrations on a database copy. For destructive changes, prove recovery from a backup or retained source instead of assuming a reverse migration can reconstruct deleted values.

4. Replay a representative raw-data window. Compare canonical counts, rejected records, monetary values, and duplicate handling between parser versions. Use explicit data validation methods to distinguish expected corrections from silent loss.

Verify contracts before declaring stability

5. Test reporting and AI questions. Check answers, not just whether SQL executes. Include historical-price questions, missing-value cases, and authorization failures so a renamed field doesn't change the meaning of a result.

6. Exercise compatibility views. For every renamed field, verify the legacy projection and identify consumers still using it. Remove the view only after their owners confirm migration.

If you're defining extraction contracts at ingestion, our AI Extraction, autoparse, lets you describe fields and receive JSON in exactly the structure you define, without writing or maintaining selectors. That output still needs type checks and business validation before it enters canonical tables.

Freeze the initial schema version only when every check passes and every exception has a written rationale and owner. Freezing means establishing a supported contract, not promising the database will never change.

Our RESTful API comes with detailed documentation for integrating extraction into your ingestion workflow. Visit Scrappey to define a structured extraction contract, connect it to a raw landing layer, and validate the resulting records before promoting them into your production schema.