> ## Documentation Index > Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # Create a Multi-region Database Schema export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; 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 tutorial. ## Before you begin Before you begin this section, complete the previous section of the tutorial, MovR: A Global Application Use-Case. We also recommend reviewing CockroachDB's multi-region capabilities, 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. The database schema used in this application is a slightly simplified version of the `movr` database schema that is built into the `cockroach` binary. The schemas are similar, but they are not the same. ## 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, 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*, *database regions*, and *table localities*. ### Cluster and database regions When you add a node to a cluster, you can assign the node a specific 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. After a cluster is deployed, you can assign regions to new and existing databases in the cluster. Only cluster regions specified at node startup can be used as database regions. You can view regions available to databases in the cluster with `SHOW REGIONS FROM CLUSTER`. Here is the `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"; ``` Note that `movr` has the following 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. By default, CockroachDB uses the table locality setting `REGIONAL BY TABLE IN PRIMARY REGION` for all new tables added to a multi-region database. The `REGIONAL BY TABLE` 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. For `REGIONAL BY ROW` tables, CockroachDB automatically assigns each row to a region based on the locality of the node from which the row is inserted. It then optimizes subsequent read and write queries executed from nodes located in the region assigned to the rows being queried. 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, of `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; ``` Note that the `vehicles` table has a foreign key constraint 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; ``` Note that, 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. ## See also * [`movr-flask` on GitHub](https://github.com/cockroachlabs/movr-flask) * CockroachDB terminology * Replication Controls * `CONFIGURE ZONE` * Define Table Partitions * `ALTER TABLE... PARTITION BY`