Consider implementing a system to allow a running coach to maintain training logs for each of the runners under his guidance. Each runner is identified by first name and runs several times per week. A run is characterized by the distance and the average pace. The coach will want to monitor week by week variations and progress. Of course many designs for the logical data model will work, but we consider just two here…
Single flat relational table…
create table reln_training_logs ( first_name varchar2(20) not null, week number not null, run number not null, distance number not null, pace number not null ); alter table reln_training_logs add constraint reln_training_logs_pk primary key (first_name,week,run) using index;
Relational table with multilevel collection column…
create type run_t as object ( distance number, pace number ); / create type weeks_running_t is varray(20) of run_t not null; / create type training_log_t is varray(255) of weeks_running_t not null; / create table nested_training_logs ( first_name varchar2(20) primary key, training_log training_log_t );
The reln_training_logs approach
would be suitable if the typical access was for ad hoc queries across runners,
and the nested_training_logs approach
would be suitable if the typical access was to report all the information
for each of a number of selected runners.
We'll look at code to populate and to report on the nested_training_logs
table. And then we'll see how table functions can be written to "view"
nested_training_logs as reln_training_logs
and to "view" reln_training_logs
as nested_training_logs. By writing
each with a ref cursor input parameter
we can conveniently test that the result of two successive transformations
is identical to the starting data. See sample
code script for the complete working code.
Following are the important steps involved in the working sample code script in little more detail.