When designing a database, it is important to consider which tables each user should be able to access. Access to tables is granted or revoked through authorizations. The highest level of authority is the system administration authority (SYSADM). A user with SYSADM authority can assign other authorizations, including the database administrator authority (DBADM).
There are other requirements that you may have to consider during your design, such as audit, history, summary, security, data typing, and parallel processing capability.
For audit purposes, you may have to record every update made to your data for a specified period. For example, you may want to update an audit table each time an employee's salary was changed. Updates to this table could be made automatically if a trigger was established to enforce this behavior. Another way to carry out audit activities is through the use of the DB2 audit facility. See Chapter 5. "Auditing DB2 Activities" for more information.
For performance reasons, you may only want to access a selected amount of data, while maintaining the base data as history. You should include within your design, the requirements for maintaining this historical data, such as the number of months or years of data that is required to be available before it can be purged.
There may be situations identified within your design that deal with summary information. For example, you may have a table that has all of your employee information in it. However, you would like to have have the employee information divided into separate tables by division or department. In this case, a summary table for each division or department based on the data in the original table would be helpful. See "Creating a Summary Table" for more information on summary tables.
Security implications should also be identified within your design. For example, you may decide to support user access to certain types of data through security tables. You can define access levels to various types of data and who can access this data. Confidential data such as employee and payroll data, would have stringent security restrictions imposed where only a select number of individuals could be authorized to view this data, whereas certain time reporting data could be set up to be viewed globally. For more information on security and authorizations, see Chapter 4. "Controlling Database Access".
You can create tables that have a structured type associated with them. With such typed tables, you can establish a hierarchical structure with a defined relationship between those tables called a type hierarchy. The type hierarchy is made up of a single root type, supertypes, and subtypes.
A reference type representation is defined when the root type of a type hierarchy is created. The target of a reference is always a row in a typed table or view.
See Chapter 3. "Implementing Your Design" for more information on implementing a design that includes typed rows and tables. See Chapter 6. "Utilities for Moving Data" for more information on moving data between typed tables that are in a hierarchical structure.
As your business grows, you may need the additional capacity and performance capability provided by DB2 Extended Enterprise Edition. In this environment, your database is partitioned across several machines or systems, each responsible for the storage and retrieval of a portion of the overall database. In this environment, each partition (or node) of the database works in parallel to handle SQL or utility operations.
Issues and considerations relating to parallel operations are presented as appropriate to the topics presented in the following chapters. These issues and considerations are typically found toward the end of each topic.