Prepare the Logical Schema Script

Designing the Logical Schema

Designing the logical schema for a Vertica database is essentially an exercise in dimensional modeling, the industry standard for data warehousing. See The Logical Schema (Tables and Constraints) for details.

Creating the Logical Schema Script

To create your logical schema, you must prepare an SQL script (plain text file, typically with an extension of .sql) that:

1. creates the tables in your database using the CREATE TABLE command
2. defines the necessary constraints and ALTER TABLE command

You can generate a script file using:

• a schema designer application
• a schema extracted from an existing database
• a text editor
• one of the example database example-name_define_schema.sql scripts as a template (see the example database directories in /opt/vertica/doc/)

In your script file, make sure that:

• each statement ends with a semicolon
• you are using only data types supported by Vertica (see the SQL Reference Manual for details)

Once you have created a database, you can test your schema script by executing it. If you encounter errors, simply drop all tables, correct the errors, and execute it again.