Database Schema Design Overview

This document provides the basics of MatrixOne database schema design. This document introduces terminology related to MatrixOne databases and subsequent data read and write examples.

Key concept in MatrixOne

Database Schema: The database schema mentioned in this article is the same as the logical object database. It is the same as MySQL.

Database

A database in MatrixOne is a collection of objects such as tables.

To view the default database contained by MatrixOne, ues SHOW DATABASES; statment.

To create a new database, ues CREATE DATABASE database_name; statement.

Table

A table is a collection of related data in a database.

Each table consists of rows and columns. Each value in a row belongs to a specific column. Each column allows only a single data type. To further qualify columns, you can add some constraints.

Index

An index is a data structure used to find data in database tables quickly. It can be seen as a ‘table of contents’ that contains pointers to the data of each row in the table, making it possible for queries to locate data that meets specific conditions more quickly.

The indexes commonly used in databases include primary key indexes, secondary indexes, etc. Among them, unique indexes are used to ensure the uniqueness of specific columns or combinations of columns, ordinary indexes are used to improve query performance, and full-text indexes are used for full-text search in text data.

There are two common types of indexes, namely:

• Primary Key: Primary key index, that is, the index that identifies the primary key column. The primary key index uniquely identifies each row of data in the table.
• Secondary index: Secondary index is the index identified on the non-primary key. The secondary index, also called a non-clustered index, is used to improve query performance and speed up data retrieval.

Other supported logical objects

MatrixOne supports the following logical objects at the same level as table:

• View: a view acts as a virtual table, whose schema is defined by the SELECT statement that creates the view.

• Temporary table: a table whose data is not persistent.

Access Control

MatrixOne supports both user-based and role-based access control. To allow users to view, modify, or delete data, for more information, see Access control in MatrixOne.

Object limitations

Limitations on identifier length

Limitations on a single table

Limitations on data types

For more information on data types, see Data Types.

Number of rows

MatrixOne supports an unlimited number of rows by adding nodes to the cluster.