Modeling Your database

The first thing you must do is define how the user will see the data in your data source. This step involves understanding how the data from your data source will be used. Define the tables, and for each table, define the columns. You may also define stored procedures and scalar functions that are applicable to your data source.

An important point to keep in mind is that you are creating a view of your data source. The data will remain in your data source, but it will be accessed through the view you are exposing. Since you are only creating a view, normalization in the strict relational theory sense is not necessary and sometimes should be avoided since join operations can be very expensive. Normalization is important when actual physical storage of the data is involved.

Also, a view can be more than just the data that is stored in your data source. It can be the data that is processed by some simple or complex algorithms. For example, if you want to support retrieval of historical data that is based on user specified time intervals, then you can expose a table that contains the columns NAME, VALUE, and TIME, along with a virtual column INTERVAL to allow the query to specify the interval. A query would look like:

This query will generate hourly averages because INTERVAL=60 was specified. The IP would use the INTERVAL=60 part of the query to retrieve the interval to use for averaging.

Once you have a model for your database, you need to decide whether to use the OpenAccess SDK Schema Database or to implement a custom schema management function in the IP. If you are using the OpenAccess SDK Schema Database, you can configure the Schema Database with the definitions of the tables. This can be done through DDL commands or INSERT statements. If you are using the IP to manage the schema, then you will have to implement the code to provide the required information to the OpenAccess SDK SQL engine.