SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-13
Determining a Database Layout
Determining a Database Layout
In your database scheme, users and applications can access the database with these:
•
Base tables only
•
Views only
•
A combination of base tables and views
In addition, indexes can be an efficient underlying mechanism for data access.
Using Base Tables
You can use base tables to externalize all the data to the user. Using base tables is the
most direct method because views, constraints, and indexes ultimately depend on their
underlying tables.
Advantages to using only base tables as the external database scheme include these:
•
Security schemes and access control are simplified.
•
All other SQL objects (views, indexes, constraints, and programs) are directly or
indirectly dependent on the base tables.
•
Recovery and management methods are simplified.
Disadvantages to using only base tables as the external database scheme include
these:
•
A user who has read access to a table can read any data in that table. Column
masking can be done only at the application level because SQLCI queries are not
restricted in reading data.
•
Alterations to tables generally have a greater impact on application code that uses
the tables directly. For example, an application should not use SELECT * to
retrieve values from a table, because adding a column to the table would mean
that the application must change.
Using Views
A view is a logical table derived from one or more base tables. A view can include a
projection of columns and a selection of rows from the table or tables. You can project
columns and select rows for a view directly from the base tables or indirectly through
other views. You can create more than one view on a table or combination of tables.
You can use views to externalize some or all the data to the user.
A view can have the same structure as an underlying base table, or the view can be
different. Views do not store data physically on the disk.
In general, views have these advantages and uses: