Data Modeling Workshop

A data model outlives almost every other design decision, so it deserves a deliberate session rather than a rushed schema. This workshop brings domain experts and engineers together to move from concepts to tables or documents, and to test the result against real questions the system must answer.

Learning goals

• Identify entities and relationships from business language
• List the access patterns the model must support
• Translate the conceptual model into a physical design
• Validate the design with sample data and queries

Step-by-step procedure

1. Start with a glossary. Ask domain experts to describe the business in plain sentences and underline the nouns. These are candidate entities.
2. Draw entities as boxes and relationships as lines, with cardinality such as one to many written on each line.
3. For each entity, list identifying attributes and the facts it owns. Question every attribute that appears on two entities.
4. Write down access patterns as questions: show a customer all open orders, find invoices overdue by thirty days, count signups per week.
5. Review the lifecycle of each entity: how it is created, changed and archived, and which changes must be audited.
6. Choose a physical design. In a relational store normalise first and denormalise only for a measured need. In a document or key value store, shape records around the access patterns.
7. Decide on keys, indexes, constraints and retention.
8. Load sample data and run each access pattern as a query. Fix gaps and record the decisions.

Worked example

A subscription business describes customers, plans and invoices. The group draws Customer to Subscription as one to many, Subscription to Plan as many to one, and Subscription to Invoice as one to many. An attribute called price appears on both Plan and Invoice, and the discussion reveals why: prices change, but old invoices must keep the price charged at the time. The team keeps the price on the invoice line as a copied value and treats Plan price as current only. A pattern for overdue invoices needs an index on status and due date, which they add. Testing the monthly revenue question exposes that cancelled subscriptions lacked an end date, so they add one with a constraint.

Exercises and templates

• Model a small domain you know, such as a library or booking system, in under an hour.
• Write five access patterns and check each against your model.
• Identify one attribute that needs history and decide how to store it.

Template:

CREATE TABLE invoice_line (
  id BIGINT PRIMARY KEY,
  invoice_id BIGINT NOT NULL REFERENCES invoice(id),
  plan_id BIGINT NOT NULL REFERENCES plan(id),
  unit_price_cents INTEGER NOT NULL,
  quantity INTEGER NOT NULL
);

Common pitfalls

• Modelling from the user interface rather than the business, which bakes screens into the schema.
• Over normalising until common queries need six joins, or under normalising until updates need six places.
• Leaving out time: history, effective dates and soft deletion are painful to retrofit.
• Skipping the review of names. Inconsistent naming creates confusion for years.

Follow-up and continuous improvement

Keep the model diagram and the decision notes in the repository, and review them whenever a new feature touches the data. Run the access pattern queries as part of your test suite with a realistic sample data set so that index and shape regressions are caught early.

Adapting this to your team

Treat everything above as a starting point rather than a rulebook. Smaller teams can compress the sessions and merge roles, while larger organizations may need written templates, a shared calendar and a named owner for the practice. Begin with one pilot on a real project, collect honest feedback from participants, and adjust the format before you roll it out more widely. Consistency matters more than perfection, so favour a simple routine you will actually repeat.

Key takeaways

• Use the language of the business to name entities
• Access patterns decide the physical design
• Copy values that must stay frozen in time, and reference values that should stay current
• Test the model with real questions before building