Overview of database designer

This topic provides an overview of database designer in OTCAD.

1: Database designer

A tool that analyzes a logical schema definition, sample queries, and sample data; and creates a physical schema (projections) that you can deploy manually.

1 - Database designer

This topic provides an overview of database designer in OTCAD.

About database designer

Use database designer to create designs that provide excellent performance and specific queries while using disk space efficiently. A design is a physical storage plan that optimizes query performance.

The Database designer:

Analyzes your logical schema, sample data, and sample queries.
Creates a physical schema design (a set of projections) that can be deployed manually.
Does not require specialized database knowledge.
Can be run and rerun any time for additional optimization without stopping the database.
Uses strategies to provide optimal query performance and data compression.

The database designer recommends projection statements for all tables referenced in the selected queries to ensure optimal query performance. Up to 100 queries can be provided as input.

Select the More options button on the Home page. Choose Database designer. The Database designer page appears with the Projections and Designs tabs.

Only administrators have the privilege to execute the database designer.

View projection details

The Projections tab lists the following information:

Projection name - The name of the projection.
Type - The method in which the projection was created. By default, projections of type DESIGNER that are created by the database designer are displayed.
Table name - The name of the projection's anchor table.
Schema - The name of the schema that contains the projection.
Creator - The name of the projection's owner.
Segmented - Specifies whether the projection is segmented.
Up to date - Specifies whether projection data is up to date. Projections that are up to date are available to participate in query execution.

In the Projections tab, you can view the projection details.

Select the Details icon.

The projection statement is listed.
Select Copy to copy the projection statement to the clipboard and analyze it in the SQL editor.
Select Download to download the projection statement to a file with .sql extension and analyze the projection statements outside the application.
Select >> to close the projection details.

Create a design

To create a design, do the following in the Database design page:

Select +Create a design.
In the Design name field, enter a unique name for the design. All designs must have unique names and should be no more than 32 characters long. Identifiers (names) of objects such as schema, table, projection, column names, and so on can be up to 128 bytes in length.
Note
```
     Ensure that the design name complies with the design naming standard.
```
The sample SQL queries are listed.
Select one of the options - Last 10, Last 50, or Last 100 to load the last 10, last 50, or last 100 SQL queries respectively.

Select the required SQL query to view the query details.
Select Create.

The design is created and displayed in the Designs tab of the database designer page.

View design details

The Designs tab lists the following information:

Design name - The unique name of the design.
Last updated on - The timestamp at which the design was last updated.
Type - The type of the design.
Status - The current status of the design. Valid values are Building, Built, and Error.

In the Designs tab, you can view the design details.

Select the Details icon.

The projection statement is listed in the Projection statement tab.
Select Copy to copy the projection statement to the clipboard and analyze it in the SQL editor.
Select Download to download the projection statement to a file with .sql extension and analyze the projection statements outside the application.
Select the Analysis tab to view information about the events and the timestamp.
Select the Sample queries tab to view information about the SQL query and the query ID.
Select >> to close the projection details.

Delete a design

You can delete a design that is no longer in use or required.

In the Database designer page, select the Designs tab.
Select ⋮ and Delete.
In the Confirmation dialog, select Delete.

This deletes the selected design and all it's configurations. The projections are not deleted.