When One Table Is Better Than Ten Thousand

Database design decisions made early in a project often have long-term consequences. Choices that appear simple or intuitive at the time can introduce significant complexity as a system grows.

In one project around ten years ago, a developer proposed creating a separate table for each batch of manufactured components. Each table would have an identical structure, with new tables created as new batches were produced.

The intention was straightforward: keep each batch isolated in its own table. From a development perspective, this can feel like a clean and logical approach, particularly when working with one batch at a time.

In this case, the developer was working primarily with object-oriented languages, and the idea of treating each batch as a separate object translated naturally into a separate table. Within that mental model, the design made sense.

However, even at that stage, it was clear that the number of batches would grow over time. The proposal would eventually have resulted in tens of thousands of tables within the same schema.

At that point, the issue becomes one of scale.

The underlying problem is that the data model is being shaped around the batching process rather than the data itself. Each row represents the same type of entity, with the same structure, regardless of batch. In that situation, the natural model is a single table.

Creating a table per batch introduces a range of problems.

From a schema management perspective, the database quickly becomes difficult to work with. Data dictionary views fill with large numbers of objects, and routine operations such as querying metadata or managing storage become more expensive. Any structural change, such as adding a column, must be applied across every table.

From a query perspective, the model breaks down even more quickly. Any requirement to analyse data across batches requires dynamic SQL or large UNION operations across many tables. Reporting and audit queries become unnecessarily complex.

There are also implications for the optimiser. Oracle’s cost-based optimiser works best when it has visibility of the full dataset and consistent statistics. Spreading data across many separate tables limits its ability to make effective decisions for queries that span multiple batches.

Operationally, the approach creates ongoing overhead. Each new batch requires DDL to create tables and indexes, apply permissions, and gather statistics. Backup and recovery processes must account for a constantly growing number of objects.

In this case, the alternative was relatively simpl: a single table with a batch identifier column.

Partitioning could also have been used if scale or operational requirements later justified it. Queries targeting a single batch can benefit from partition pruning, accessing only the relevant portion of the data, while queries across batches can be expressed naturally using standard SQL.

Schema management becomes significantly simpler. Structural changes are applied once, rather than repeated across thousands of objects.

Operationally, partitioning also provides flexibility. Individual batches can be managed using partition-level operations, and data can be archived or removed without affecting the rest of the table.

In this case, the issue was identified early, before the system had been built out at scale. That made the correction straightforward. Had the design been implemented and allowed to grow, the cost of changing direction would have been significantly higher.

The broader lesson is that database design should reflect the nature of the data, not just the process that produces it. When data shares a common structure and purpose, it is usually better modelled within a single table, with appropriate mechanisms such as partitioning used to handle scale and organisation.

This is an extreme example, but it illustrates a pattern that appears in many projects. Most design issues are less dramatic, but they can still introduce unnecessary complexity, reduce flexibility, and create problems that are expensive to resolve later.

More importantly, this type of decision is often made before a database specialist is involved. Bringing experienced input into the design stage can prevent problems that are expensive to fix later.