Filter
Top Products
2.2 Designing a Database
To create a database, first design the database. When designing the database, be sure to carefully analyze the
business applications, types and amounts of data to be processed, and data processing methods.
The database design procedure includes steps for designing tables, attributes, simplified storage structures, and
storage structures.
Designing tables
Design tables that meet the needs of the business application.
Designing attributes
Determine appropriate data types for designed items, as well as possible column constraints and default values. For
details about the attributes that can be used, refer to the "SQL Reference Guide."
Designing the storage structure
Determining the storage structure
From an application program viewpoint, the database is represented in table format. The application program
performs data operations according to structured query language (SQL) statements as if it were manipulating rows
and columns of data.
The structure for storing the data represented in table format on physical pages is called the storage structure. An
appropriate database storage structure is designed according to the analysis data of the access path. To design such
a database storage structure, obtain the size of the table from the amount of data and determine the required amount
of disk space. The use of a split table is determined by estimating the amount of data to be added and the operation
time acceptable for database reorganization.
For details about storage structures, see Chapter 4 "Storage Structure."
· SEQUENTIAL structure
· RANDOM structure
· OBJECT structure
· BTREE structure
Estimating the amount of database space required for each storage structure
In this step, allocate database space. Carefully consider the amount of data to be processed and the area access
patterns, then allocate database space for each component of the storage structure.
For details about estimating the amount of database space required for each storage structure, see 4.4 "Estimating
the Required Amount of Database Space."
Designing a simplified storage structure
To simplify the process of defining the storage structure, specify the database space for storing data by defining the
table or index. SymfoWARE/RDB then automatically defines the storage structure. In this case, the SEQUENTIAL
structure is used as the table storage structure. For a table of multimedia data, the SEQUENTIAL or OBJECT
structure can be selected as the table storage structure. This process does not allow the use of split storage.
If the storage structure is simplified defined, the DSO and DSI names of the table are automatically assigned from the
names generated by the table or index definitions. The data length and allocation are automatically determined at this
time.
For simplified definition of the storage structure, the capacity of DSI is dynamically expanded.
The naming prefix, data length, allocation amount, and DSI capacity expansion setting can all be changed by the use
of operating environment file parameters. For a table of multimedia data, the storage structure can also be selected.
For information about the operating environment file, refer to the "RDB User's Guide: Application Program
Development."
Examples of designing the database
Figure: Contents of the inventory management database shows an inventory management database for a retail store.
This inventory management database is used as an example for explaining database design in this section. This
database is used to implement the inventory management applications of the retail store. The inventory management
database consists of three tables, as follows.
16