Defining Your Database Schema: A Guide to Different Methods

When it comes to creating a database schema, there are several ways to approach it. The most common approach is to use a specific database management system SQL version to write SQL commands to define the schema. However, there are also declarative approaches, such as YAML and XML, that offer alternative ways to define the schema. In this article, we'll explore different ways to define a database schema, including declarative schema definition.

SQL

One of the most common ways of defining a database schema is through SQL (Structured Query Language). SQL is a language that is used to communicate with relational databases. With SQL, you can create tables, define columns and data types, specify constraints, and more. SQL is a powerful and flexible language, but it requires some specific knowledge in a particular database engine.

Here is an example how we can define schema for an imaginable project that should contain the users and posts tables:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ORM

Another way of defining a database schema is by using an ORM (Object-Relational Mapping) framework. In ORM, the database schema is mapped to an object-oriented model. With an ORM, you can define your database schema using a high-level language, such as Python or Java. The ORM framework will then generate the SQL code to create the database schema. Such frameworks are popular because they can simplify the development process, reduce the amount of boilerplate code, and make it easier to work with databases in an object-oriented way.

Let’s rewrite the example above for Hibernate framework, which is popular in Java world:

@Entity
@Table(name = "users")
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  
  @Column(name = "name", nullable = false)
  private String name;
  
  @Column(name = "email", nullable = false, unique = true)
  private String email;
  
  @Column(name = "password", nullable = false)
  private String password;
  
  @Column(name = "created_at", nullable = false, updatable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
  private LocalDateTime createdAt;
  
  // getters and setters
}

@Entity
@Table(name = "posts")
public class Post {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  
  @ManyToOne
  @JoinColumn(name = "user_id", nullable = false)
  private User user;
  
  @Column(name = "title", nullable = false)
  private String title;
  
  @Column(name = "content", nullable = false)
  private String content;
  
  @Column(name = "created_at", nullable = false, updatable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
  private LocalDateTime createdAt;
  
  // getters and setters
}

Graphical User Interface

Many database management systems (DBMS) come with a graphical user interface (GUI) that allows you to define your database schema visually. With a GUI, you can drag and drop tables and columns, set relationships, and define constraints. This approach can be useful for those who are not familiar with SQL or programming, as it provides a more user-friendly way of defining the database schema.

You can see tons of colorful pictures around the net that demonstrate this approach.

Declarative Schema Definition Languages

Declarative schema definition languages like YAML and XML offer a more human-readable way for defining a database schema than plain-SQL. With YAML and XML, you can define your schema using a structured format that is easy to read and understand. These languages allow you to define tables, columns, relationships, and constraints in a way that is easier to read by the human eyes and actually does not require any deep knowledge in SQL.

tables:
  - name: users
    columns:
      - name: id
        type: serial
        options:
          - primary_key
      - name: name
        type: varchar(50)
        options:
          - not_null
      - name: email
        type: varchar(255)
        options:
          - not_null
          - unique
      - name: password
        type: varchar(255)
        options:
          - not_null
      - name: created_at
        type: timestamp
        options:
          - default: "CURRENT_TIMESTAMP"
  - name: posts
    columns:
      - name: id
        type: serial
        options:
          - primary_key
      - name: user_id
        type: integer
        options:
          - references: users(id)
          - not_null
      - name: title
        type: varchar(255)
        options:
          - not_null
      - name: content
        type: text
        options:
          - not_null
      - name: created_at
        type: timestamp
        options:
          - default: "CURRENT_TIMESTAMP"

Declarative schema definition languages offer several benefits, including:

Readability: YAML and XML files are easy to read and understand, making it easier to collaborate and maintain the database schema.

Portability: YAML and XML files can be easily moved between different database management systems, different platforms, etc. So you can easily switch to a new system if needed.

Version control: YAML and XML files can be stored in version control systems, allowing comfortable collaboration and tracking changes over time.

It is worth mentioning that YAML and XML have been widely used in database schema versioning tools for years. XML is more popular, but I personally found YAML more readable and easier to share with Git and compared in a diff-like way. When the project employs the PostgreSQL database, I usually go with this tool to define the schemas and deal with schema migration as well.

In conclusion, there are several ways to define a database schema, and the choice depends on your level of expertise, preference, and specific needs. Declarative schema definition languages like YAML and XML offer a more human-readable and portable way of defining a database schema that is easy to maintain and collaborate on.