Jargon for Physical Data Modelling of SQL Databases

Jargon is an opinionated platform for designing APIs and Data Models based on principles from Domain Driven Design. It’s primary focus is on developing business-oriented Domains that are simple and clear.

But Jargon can also design physical data models, and generate them into SQL statements to populate a relational database.

A note on SQL interoperability

Not all SQL statements are compatible with all database systems. Jargon aligns to PostgreSQL syntax for both SQL generation and importing.

Let’s take a look at how it works:

Enabling SQL Generation

You need to configure Jargon to enable SQL generation, which you can do on the Domain’s settings page. After clicking the ‘Generate SQL schema’ checkbox (and saving the settings) the next time you save your Domain, Jargon will generate the SQL CREATE TABLE statements for you.

The generated SQL is available with the rest of the Domain’s artefacts:

Now that we’ve enabled SQL generation, let’s take a look at a Domain and then the generated SQL. We’ll use the Domain from our article on advanced modelling techniques as it’s got some interesting design elements in it:

Domain model from the Advanced Modelling Techniques article

Without making any changes, here’s the generated SQL that Jargon produces. Jargon attempts to order the CREATE TABLE statements so that the tables pointed to with foreign keys are defined before they’re used. But just incase that doesn’t work, all the tables have a IF NOT EXISTS:

--
-- Dog
--
CREATE TABLE IF NOT EXISTS Dog (
    favouriteToy    text,
    name            text PRIMARY KEY
);

--
-- Cat
--
CREATE TABLE IF NOT EXISTS Cat (
    favouriteSpot    text,
    name             text PRIMARY KEY
);

--
-- Elephant
--
CREATE TABLE IF NOT EXISTS Elephant (
    favouriteFood    text,
    name             text PRIMARY KEY
);

--
-- Registration
--
CREATE TABLE IF NOT EXISTS Registration (
    id_of_Registration    smallserial PRIMARY KEY,
    expiration            date
);

--
-- VehicleDetail
--
CREATE TABLE IF NOT EXISTS VehicleDetail (
    id_of_VehicleDetail    smallserial PRIMARY KEY,
    numberOfWheels         numeric,
    numberOfDoors          numeric
);

--
-- Car
--
CREATE TABLE IF NOT EXISTS Car (
    sunroof           boolean,
    licensePlate      text PRIMARY KEY,
    vehicleDetails    smallserial REFERENCES VehicleDetail(id_of_VehicleDetail),
    registration      smallserial REFERENCES Registration(id_of_Registration)
);

--
-- Truck
--
CREATE TABLE IF NOT EXISTS Truck (
    airHorn           boolean,
    licensePlate      text PRIMARY KEY,
    vehicleDetails    smallserial REFERENCES VehicleDetail(id_of_VehicleDetail),
    registration      smallserial REFERENCES Registration(id_of_Registration)
);

--
-- Pet
-- This is an abstract class, and only has its primary key
-- and foreign keys for each of the concrete implementations
--
CREATE TABLE IF NOT EXISTS Pet (
    name        text PRIMARY KEY,
    Dog         text REFERENCES Dog(name),
    Cat         text REFERENCES Cat(name),
    Elephant    text REFERENCES Elephant(name)
);

--
-- Vehicle
--
CREATE TABLE IF NOT EXISTS Vehicle (
    licensePlate      text PRIMARY KEY,
    vehicleDetails    smallserial REFERENCES VehicleDetail(id_of_VehicleDetail),
    registration      smallserial REFERENCES Registration(id_of_Registration)
);

--
-- Person
--
CREATE TABLE IF NOT EXISTS Person (
    id         text PRIMARY KEY,
    vehicle    text REFERENCES Vehicle(licensePlate)
);

--
-- Person.pets
-- A table for the array relationship of Person.pets
--
CREATE TABLE IF NOT EXISTS Person_pets (
    id      text REFERENCES Person(id),
    pets    text REFERENCES Pet(name)
);

It all seems pretty ordinary, but there are some important design decisions that Jargon has made to bridge the gap between Domain modelling and physical table design.

The biggest change is with the abstract Pet Class:

--
-- Dog
--
CREATE TABLE IF NOT EXISTS Dog (
    favouriteToy    text,
    name            text PRIMARY KEY
);

--
-- Pet
-- This is an abstract class, and only has its primary key
-- and foreign keys for each of the concrete implementations
--
CREATE TABLE IF NOT EXISTS Pet (
    name        text PRIMARY KEY,
    Dog         text REFERENCES Dog(name),
    Cat         text REFERENCES Cat(name),
    Elephant    text REFERENCES Elephant(name)
);

--
-- Person
--
CREATE TABLE IF NOT EXISTS Person (
    id         text PRIMARY KEY,
    vehicle    text REFERENCES Vehicle(licensePlate)
);

In the Domain model, Pet is a superclass of Dog, Cat and Elephant, and it’s been marked abstract. From a Domain modelling perspective, this means that any Class that uses Pet, will be able to substitute in one of the subtypes.

That’s not something that’s easily done with relational databases unfortunately. To overcome this, Jargon has create the Pet table with foreign keys to each of the subclasses. In this way a Person can still choose any one of the Pet’s subclasses, but via an intermediary table.

Another major change is that all tables need to have primary keys, even if the Domain model says they don’t.

The Registration table is an example:

--
-- Registration
--
CREATE TABLE IF NOT EXISTS Registration (
    id_of_Registration    smallserial PRIMARY KEY,
    expiration            date
);

--
-- Vehicle
--
CREATE TABLE IF NOT EXISTS Vehicle (
    licensePlate      text PRIMARY KEY,
    vehicleDetails    smallserial REFERENCES VehicleDetail(id_of_VehicleDetail),
    registration      smallserial REFERENCES Registration(id_of_Registration)
);

The Registration Class in the Domain model is a ValueObject — it has no business identifier. But in order to map the Domain to a physical table design, the Registration table needs to be able to uniquely identify each row. Jargon inserts a mechanical primary key (id_of_Registration), and every Class that uses Registration gets a foreign key to the column.

Another consequence is for arrays:

--
-- Pet
-- This is an abstract class, and only has its primary key
-- and foreign keys for each of the concrete implementations
--
CREATE TABLE IF NOT EXISTS Pet (
    name        text PRIMARY KEY,
    Dog         text REFERENCES Dog(name),
    Cat         text REFERENCES Cat(name),
    Elephant    text REFERENCES Elephant(name)
);

--
-- Person
--
CREATE TABLE IF NOT EXISTS Person (
    id         text PRIMARY KEY,
    vehicle    text REFERENCES Vehicle(licensePlate)
);

--
-- Person.pets
-- A table for the array relationship of Person.pets
--
CREATE TABLE IF NOT EXISTS Person_pets (
    id      text REFERENCES Person(id),
    pets    text REFERENCES Pet(name)
);

Arrays don’t work well with relational tables either. Jargon inserts a new table to store the

A note on column datatypes

Jargon picks pretty reasonable choices for the column datatypes based on the types used in your Domain. If you want to change them however, there’s a Key-Value Pair that lets you change it:

Wrapping up:

So that’s a an overview of how you can design physical table structures with jargon

If you have any further questions about generating SQL from Jargon, you can:

• check the Getting Started Guide
• leave a comment below
• raise an issue at the jargon issue tracker GitHub repository
• Sign up for a free Jargon account and try some out for yourself.