Schema Design

Editing the Database Schema

The Database Schema Editor allows administrators and database developers to view and change the database schema (the design of data tables and relationships). The Database Schema Editor offers a graphical database view showing tables as boxes and relationships as lines connecting boxes. It also offers a tabular view that lists the tables in alphabetical order and allows you to see the table columns as an easy to edit list.

When you alter the database schema your database data will remain intact. You can add, remove, rename, index, and change the type of columns. Any necessary data conversions are performed automatically.

You can also save your changes as a script which you can run later using Openisql. This is especially useful when you want to test your new schema on a test database before applying it to a production database (a database which users are actually using). You can save the changes to a script file and then save the same changes to your database.

Figure 14:Schema Editor-Table View

The Schema Editor’s table view lists the tables on the left and the table attributes on the right. A table below shows database relationships. Column attributes can be edited directly in the table view where they are listed.

Figure 15: Schema Editor - Graphic View

You can switch to the Graphical View by toggling the view button on the top left. The Graphical View provides a graphical representation of your schema. You can drag the tables around.

A map in the lower left corner allows you to quickly navigate to portions of your schema by clicking on the area you want to make visible. You can also click on the table list on the upper left to quickly locate a table. A zoom/shrink feature allows you to make your view much smaller to make tables more manageable.

To edit the table characteristics you can click on the triangle in the upper left hand corner. This provides a tabular view showing all of the columns in the table and their settings.

Table Inspector

The Table Inspector allows you to change the name of a table and set the natural data order. You can bring up the table inspector by double clicking on the table names on the left or by adding a new table.

The Natural Order of Data setting is used to fine tune the performance of your database. If you leave this setting blank the data will be generally left in insertion order. However, if you set this to a foreign key that is often used in your joins or a column often used in sorting, it can provide performance benefits.

Setting the Natural Order of Data will reorder the data during a database cleanup cycle so that multiple rows (or sequential rows) that are accessed together will be stored in the same place.

Key Values and Relationships

Database relationships have primary and foreign keys. A primary key is a value which uniquely identifies each record. Primary keys are used for selecting, updating and deleting specific records. They can also be used to define relationships between tables.

The most common type of relationship is a one-to-many. This means that a single record in one table matches multiple records in another table. Normally a column called a foreign key is created in the “many” table whose values match the primary key of the single record. This common value is what SQL uses to match records together.

It is important that primary keys have a unique index and foreign keys have regular index. This improves search performance significantly.

The database itself does not use the relationship information defined here to create joins between tables. Relationship information is only meta-data (or data for your information). However, the information is still important because your application may use this information to create the SQL that specifies the joins. Defining the relationships allows you to use them with RADstudio or WebObjects.

The graphical interface tries to restrict users to the types of relationships that can be created for use with RADstudio. By encouraging less-complex relationships we ensure that your database will be compatible with a variety of different applications (including RADstudio). Despite the restriction on this tool, any relationship that you can conceive of in SQL is supported by OpenBase.

Multi-column keys are allowed but must be defined using SQL instead of this interface. Multi-column keys are discouraged because they are less efficient than single column keys and may create some compatibility issues with some database application tools (such as RADstudio). See the section on creating primary keys later in this book for more details on creating multi-column unique keys using SQL.

Defining Relationships

Relationships can be added by clicking the “+” relationship button located at the top of the Schema Design window. This brings up a panel which allows you to define the origin and destination of your relationships.

Figure 16: Relationship Panel

Relationships are defined from bottom up. If you have a one-to-many relationship, as described above, we need to add a column (a foreign key) to the “many” table which points back to the primary key of the “one” table. Using the popups you can choose the end-points for your relationship.

A default foreign key name is automatically entered into the foreign key field. If you accept this default, the column will automatically be added to the table. You can alternatively choose an existing column in the combo box to use as your foreign key as long as the column type matches the type of the primary key in the other table.

Figure 17: Relationship Editor

The Relationship Editor window can be brought up at any time by double clicking on a relationship.

Using the Relationship Editor you can change your relationship to a oneto-one and specify a variety of options used by other programs. It is important to mention that the relationship information is only meta-data and is not actually used by the database itself. However, applications like RADstudio and WebObjects use this information to constuct data models so it is still important.

While the OpenBase SQL itself fully supports the use of multi-column keys and relationships, this administration application does not allow you to define multi-column relationships (it is generally discouraged). If this is a requirement, please use the EOModeler, SQL or your development environment to define your multi-column relationships.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License