Create a Multi-region Database Schema

This page guides you through creating a database schema for an example global application. It is the second section of the tutorial.

Before you begin

Before you begin this section, complete the previous sections of the tutorial, ending with . We also recommend reviewing , if you have not done so already.

The `movr` database

The example MovR application is built on a multi-region deployment of CockroachDB, loaded with the movr database. This database contains the following tables:

users
vehicles
rides

These tables store information about the users and vehicles registered with MovR, and the rides associated with those users and vehicles. Initialization statements for movr are defined in dbinit.sql, a SQL file that you use later in this tutorial to load the database to a running cluster.

The database schema used in this application is a slightly simplified version of the that is built into the cockroach binary. The schemas are similar, but they are not identical.

Multi-region in CockroachDB

A distributed CockroachDB deployment consists of multiple, regional instances of CockroachDB that communicate as a single, logical entity. In , each instance is called a node. Together, the nodes form a cluster.To keep track of geographic information about nodes in a cluster, CockroachDB uses , , and .

Cluster and database regions

When you , you can assign the node a specific . Localities represent a geographic region or zone, and are meant to correspond directly to the cloud provider region or zone in which the node is deployed.Each unique regional locality is stored in CockroachDB as a . After a cluster is deployed, you can assign regions to new and existing databases in the cluster. For an example of a deployment, refer to , the final step of this tutorial series.Only cluster regions specified at node startup can be used as . You can view regions available to databases in the cluster with .

Here is the statement for the movr database:

>
CREATE DATABASE movr PRIMARY REGION "gcp-us-east1" REGIONS "gcp-us-east1", "gcp-europe-west1", "gcp-us-west1";

In this example, movr has the following , which correspond to regions in Google Cloud:

gcp-us-east1 (primary)
gcp-europe-west1
gcp-us-west1

Table localities

After you have added regions to a database, you can control where the data in each table in the database is stored, using . By default, CockroachDB uses the table locality setting for all new tables added to a multi-region database. The REGIONAL BY TABLE locality optimizes read and write access to the data in a table from a single region (in this case, the primary region gcp-us-east1). The movr database contains tables with rows of data that need to be accessed by users in more than one region. As a result, none of the tables benefit from using a REGIONAL BY TABLE locality. Instead, all three tables in the movr database schema should use a . With REGIONAL BY ROW locality, CockroachDB initially assigns each row of the table to a region based on the locality of the node that inserted the row. Subsequent reads and writes are optimized based on the localities of the nodes querying those rows.

As shown in the CREATE TABLE statements below, the REGIONAL BY ROW clauses do not identify a column to track the region for each row. To assign rows to regions, CockroachDB creates and manages a hidden , of type crdb_internal_region. The values of crdb_region are populated using the regional locality of the node from which the query creating the row originates.

The `users` table

Here is the CREATE TABLE statement for the users table:

>
CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  city STRING NOT NULL,
  first_name STRING,
  last_name STRING,
  email STRING,
  username STRING,
  password_hash STRING,
  is_owner bool,
  UNIQUE INDEX users_username_key (username ASC))
  LOCALITY REGIONAL BY ROW;

The `vehicles` table

>
CREATE TABLE vehicles (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
    type STRING,
    city STRING,
    owner_id UUID,
    date_added date,
    status STRING,
    last_location STRING,
    color STRING,
    brand STRING,
    CONSTRAINT fk_ref_users FOREIGN KEY (owner_id) REFERENCES users (id))
    LOCALITY REGIONAL BY ROW;

The vehicles table has a on the users table, for the city and owner_id columns. This guarantees that a vehicle is registered to a particular user (i.e., an “owner”) in the city where that user is registered.

The `rides` table

>
CREATE TABLE rides (
  id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  city STRING NOT NULL,
  vehicle_id uuid,
  rider_id uuid,
  start_location STRING,
  end_location STRING,
  start_time timestamptz,
  end_time timestamptz,
  length interval,
  CONSTRAINT fk_city_ref_users FOREIGN KEY (rider_id) REFERENCES users (id),
  CONSTRAINT fk_vehicle_ref_vehicles FOREIGN KEY (vehicle_id) REFERENCES vehicles (id))
  LOCALITY REGIONAL BY ROW;

Like the vehicles table, the rides table has foreign key constraints. These constraints are on the users and the vehicles tables.

Next steps

Now that you are familiar with the movr schema, you can .

Get Started

CockroachDB and AI

Feature Overview

Data Resilience

Connect to an Application

Self-Hosted Deployments

Schema Design

Reads and Writes

Stream Data

Cross-Cluster Replication

Multi-Region Capabilities

Optimize Performance

Integrate

Create a Multi-region Database Schema

Before you begin

The `movr` database

Multi-region in CockroachDB

Cluster and database regions

Table localities

The `users` table

The `vehicles` table

The `rides` table

Next steps

See also

​Before you begin

​The movr database

​Multi-region in CockroachDB

​Cluster and database regions

​Table localities

​The users table

​The vehicles table

​The rides table

​Next steps

​See also

Before you begin

The `movr` database

Multi-region in CockroachDB

Cluster and database regions

Table localities

The `users` table

The `vehicles` table

The `rides` table

Next steps

See also