Deriving a Data Model from OpenAPI

Quick answer: Deriving a data model from OpenAPI means turning the schemas into relational entities, foreign keys, and many-to-many link tables, then generating DDL, indexes, deterministic seed data, and deployment scripts for MySQL, SQL Server, and Oracle. This closes the gap between the API contract and the database. When business logic cannot be inferred unambiguously, the result raises an open question rather than guessing.

Why the API and the database drift apart

The OpenAPI document describes the shapes the API sends and receives, while the database holds the tables and keys that persist them. The two are related but not identical, and a team that reads only the contract can miss the foreign keys, the link tables, and the indexes the storage needs.

The modeling layer reads the schemas and reconstructs the relational design so the contract and the database can be compared rather than maintained as separate guesses.

What the derivation produces

The dialects are the three supported databases, and the same source spec feeds each one rather than a separate hand-written design per engine.

Take an order schema that references a customer. The derivation resolves the customer entity, places a foreign key on the order table, and when the order also lists many products, it proposes the link table for the order-to-product relationship. The same source then renders the table and its keys in each dialect. A reviewer reads the proposed relationships against the business rules rather than reconstructing them, and anything the schemas do not pin down stays an open question rather than a forced column.

How the relationships are resolved

The derivation follows the structure rather than inventing it.

1. Read the schemas and identify the entities.
2. Resolve references and the foreign keys between them.
3. Infer many-to-many link tables from the repeated relationships.
4. Add indexes for the access paths the operations use.
5. Mark anything ambiguous as an open question.

A many-to-many link table is a good example of inference. When two entities reference each other in a way that implies a join, the modeling layer proposes the link table rather than forcing the relationship into one side. The proposal stays reviewable, and it does not execute.

Where ambiguity is handled honestly

The tool does not guess when the business logic could be read more than one way. A relationship that could be a foreign key or a standalone value, or a cardinality that the schemas do not pin down, raises an open question rather than a confident table. That keeps the generated model honest and points the reviewer at the exact decision that needs a person.

This is the same boundary that keeps static signals from being written as guarantees. The derived model is a proposal grounded in the schemas, and it still needs reconciliation against the live database and a review before any change runs.

Powerduck keeps the data model as a supporting capability behind the core workflows, using it to connect the API contract to the storage rather than to become a general database tool.

Frequently asked questions

Does the model run the SQL it generates? No. SQL is generated for review and is never auto-executed, and the modeling package has no executing driver.

Which databases are supported? MySQL, SQL Server, and Oracle, with DDL, indexes, seed data, and deployment scripts generated from one spec.

What happens when a relationship is ambiguous? It raises an open question rather than a guessed table, so the decision stays with a reviewer.

The bottom line

Deriving a data model from OpenAPI reconstructs the relational entities, foreign keys, and link tables behind the contract, and generates the DDL, indexes, seed data, and deployment scripts for three databases. Ambiguous logic stays an open question rather than a guess, and the generated SQL is never executed by the tool. This closes the gap between the API and the storage while keeping the design reviewable, and it remains a supporting capability rather than the core. Read more in the API data model guide.