Archive
Archive
VoltDB is a relational database product. Relational databases consist of tables and columns, with constraints, indexes, and views. VoltDB uses standard SQL database definition language (DDL) statements to specify the database schema. So designing the schema for a VoltDB database uses the same skills and knowledge as designing a database for Oracle, MySQL, or any other relational database product.
This guide describes the stages of application design by dividing the work into three chapters:
Design the schema in DDL to define the database structure. Schema design is covered in this chapter.
Design stored procedures to access data in the database. Stored procedures provide client applications an application programming interface (API) to the database. Stored procedures are covered in Chapter 5, Designing Stored Procedures to Access the Database.
Design clients to provide business logic and also connect to the database to access data. Client application design is covered in Chapter 6, Designing VoltDB Client Applications.
The database schema is a specification that describes the structure of the VoltDB database such as tables and indexes, identifies the stored procedures that access data in the database, and defines the way tables and stored procedures are partitioned for fast data access. When designing client applications to use the database, the schema specifies the details needed about data types, tables, columns, and so on.
Along with designing your database tables, an important aspect of VoltDB database design is partitioning, which provides much more efficient access to data and processing. Partitioning distributes the rows of a table and the processing to access the table across several, independent partitions instead of one. Your design requires coordinating the partitioning of both database tables and the stored procedures that access the tables. At design time you choose a column on which to partition a table's rows. You also partition stored procedures on the same column if they use the column to identify which rows to operate on in the table.
At runtime, VoltDB decides which cluster nodes and partitions to use for the table partitions and consistently allocates rows to the appropriate partition. Figure 4.2, “Partitions Distribute Table Data and Stored Procedure Processing” shows how when data is inserted into a partitioned table, VoltDB automatically allocates the data to the correct partition. Also, when a partitioned stored procedure is invoked, VoltDB automatically executes the stored procedure in the single partition that has the data requested.
The following sections of this chapter provide guidelines for designing VoltDB database schemas. Although gathering business requirements is a typical first step in database application design, it is outside the scope of this guide.
You use standard SQL DDL statements to design your schema. For a full list of valid VoltDB DDL, see Appendix A, Supported SQL DDL Statements. The easiest way to enter your DDL statements is using VoltDB's command line utility, sqlcmd. Using sqlcmd you can input DDL statements in several ways:
Redirect standard input from a file when you start sqlcmd:
$ sqlcmd < myschema.sql
Import from a file using the sqlcmd file directive:
$ sqlcmd 1> file myschema.sql;
Enter DDL directly at the sqlcmd prompt:
$ sqlcmd 1> 2> CREATE TABLE Customer ( 3> CustomerID INTEGER UNIQUE NOT NULL, 4> FirstName VARCHAR(15), 5> LastName VARCHAR (15), 6> PRIMARY KEY(CustomerID) 7> );
Copy DDL from another application and paste it into the sqlcmd prompt:
$ sqlcmd 1> CREATE TABLE Flight ( 2> FlightID INTEGER UNIQUE NOT NULL, 3> DepartTime TIMESTAMP NOT NULL, 4> Origin VARCHAR(3) NOT NULL, 5> Destination VARCHAR(3) NOT NULL, 6> NumberOfSeats INTEGER NOT NULL, 7> PRIMARY KEY(FlightID) 8> );
The following sections show how to design and create schema objects. DDL statements and techniques for changing a schema are described later in Section 4.6, “Modifying the Schema”.
V13.x Documentation
