> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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 [Develop and Deploy a Global Application](https://docs.cockroachlabs.com/docs/v24.3/movr.md#develop-and-deploy-a-global-application) tutorial.

## Before you begin

Before you begin this section, complete the previous sections of the tutorial, ending with [MovR: A Global Application Use-Case](https://docs.cockroachlabs.com/docs/v24.3/movr-flask-use-case).

We also recommend reviewing [CockroachDB's multi-region capabilities](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md), 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`](https://github.com/cockroachlabs/movr-flask/blob/master/dbinit.sql), a SQL file that you use later in this tutorial to load the database to a running cluster.

> [!NOTE]
>
> The database schema used in this application is a slightly simplified version of the [`movr` database schema](https://docs.cockroachlabs.com/docs/v24.3/movr) 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 [CockroachDB terminology](https://docs.cockroachlabs.com/docs/v24.3/architecture/glossary.md#cockroachdb-architecture-terms), 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 [*cluster regions*](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md#cluster-regions), [*database regions*](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md#database-regions), and [*table localities*](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md).
>
> ### Cluster and database regions
>
> When you [add a node to a cluster](https://docs.cockroachlabs.com/docs/v24.3/cockroach-start.md), you can assign the node a specific [locality](https://docs.cockroachlabs.com/docs/v24.3/cockroach-start.md#locality). 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 [cluster region](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md#cluster-regions). 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 [Deploy a Global Serverless Application](https://docs.cockroachlabs.com/docs/v24.3/movr-flask-deployment), the final step of this tutorial series.
>
> Only cluster regions specified at node startup can be used as [database regions](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md#database-regions). You can view regions available to databases in the cluster with [`SHOW REGIONS FROM CLUSTER`](https://docs.cockroachlabs.com/docs/v24.3/show-regions).

Here is the [`CREATE DATABASE`](https://docs.cockroachlabs.com/docs/v24.3/create-database) statement for the `movr` database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
>
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 [database regions](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md#database-regions), 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 [table localities](https://docs.cockroachlabs.com/docs/v24.3/multiregion-overview.md).

By default, CockroachDB uses the table locality setting [`REGIONAL BY TABLE IN PRIMARY REGION`](https://docs.cockroachlabs.com/docs/v24.3/table-localities#regional-tables) 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 [`REGIONAL BY ROW` locality](https://docs.cockroachlabs.com/docs/v24.3/table-localities#regional-by-row-tables). 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.

> [!NOTE]
>
> 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 [`crdb_region` column](https://docs.cockroachlabs.com/docs/v24.3/alter-table#crdb_region), of [`ENUM`](https://docs.cockroachlabs.com/docs/v24.3/enum) 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
>
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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
>
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 [foreign key constraint](https://docs.cockroachlabs.com/docs/v24.3/foreign-key.md) 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
>
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 [set up a development environment for the application](https://docs.cockroachlabs.com/docs/v24.3/movr-flask-setup).

## See also

* [`movr-flask` on GitHub](https://github.com/cockroachlabs/movr-flask)
* [CockroachDB terminology](https://docs.cockroachlabs.com/docs/v24.3/architecture/glossary.md#cockroachdb-architecture-terms)
* [Replication Controls](https://docs.cockroachlabs.com/docs/v24.3/configure-replication-zones.md)
* [`CONFIGURE ZONE`](https://docs.cockroachlabs.com/docs/v24.3/alter-table#configure-zone)
* [Define Table Partitions](https://docs.cockroachlabs.com/docs/v24.3/partitioning.md)
* [`ALTER TABLE... PARTITION BY`](https://docs.cockroachlabs.com/docs/v24.3/alter-table#partition-by)