How to design a data model with Yantra Studio
Build a data model at a chosen level of abstraction, give it business meaning, and check it against your standards before any SQL exists.
When this is useful
- A new schema is being designed and you want the modelling decisions recorded rather than implied by the DDL.
- An existing database needs documenting in a form business stakeholders can read.
- You need conceptual, logical and physical views of the same domain that do not contradict each other.
Before you start
- A diagram open, and a decision about level: conceptual for a discussion with the business, logical for design, physical for what will be built.
Step 1: Start at the right level
Use the quick-create templates to start a conceptual ER, logical or physical model, or start empty and insert entities directly.
Page ▸ Template galleryPage ▸ Entity / Table
Step 2: Model entities and relationships
Add entities with their attributes, and connect them to record relationships. Because these are modelled rather than drawn, the same model can produce DDL later.
Where an association carries its own attributes, model it as its own entity rather than as a decorated line — a junction that exists in the model is a junction that survives into the schema.
Page ▸ Entity / TablePage ▸ Connector
Step 3: Give it business meaning
Record definitions in the Data Dictionary and the Business Glossary, and group a large model into readable parts with Subject Areas.
This is what stops the same table meaning three different things in three different teams.
Data ▸ Data DictionaryData ▸ Business GlossaryData ▸ Subject Areas
Step 4: Check it before you build it
Validate Model checks the model, Naming Standards checks names against your organisation's conventions, and Health Report summarises the outcome.
Data ▸ Validate ModelData ▸ Naming StandardsData ▸ Health Report
What happens next
The model is ready to generate DDL for PostgreSQL, MySQL, BigQuery or SQL Server, or — if the database already exists — to produce the migration between the two.
It is also the input to lineage and impact analysis, so a later change to one column can be traced to what depends on it.
Example
A subscription billing domain modelled logically first: twelve entities, three subject areas, glossary definitions agreed with finance before a single table was created.
The naming check caught eleven inconsistencies that would otherwise have shipped into production.
Tips
- Agree the glossary before the columns. Naming arguments during implementation are usually definition arguments in disguise.
- Use the Bulk Editor for a change that touches many entities rather than editing them one at a time.
Limitations
- Validation checks against declared rules; it reports departures rather than correcting them.
- The data dictionary, glossary and naming standards are each separately enabled.