ddbicon.pngDefining Views for Use with Non-Normalized Data


"Non-normalized" files contain multiple data formats. DDBuilder, can access non-normalized files and create views that remove individual data formats from physical files and display the information as if it were combined in a normalized file format.

The following is an example of a multiple record type file that contains information for customers and orders pertaining to two customers: Bob's Western Wear (BWW) and Acme Boot Shop (ABS). Each customer has ordered two products:

BWW

0

Bob's Western Wear

12 West St.

Albany, OR

BWW

1

Felt Hat

21.95

 

BWW

2

Leather Gloves

4.80

 

ABS

0

ACME Boot Shop

9 Palm Ave.

Fallon, NV

ABS

1

Leather Gloves

4.80

 

ABS

2

Stitched Boots

50.00

 

The first column contains the company code (i.e., BWW or ABS). The second column contains the order number (i.e., 1 or 2). The data format for these columns is the same.

The third column contains both the item description and the customer name. The fourth column contains both the customer address and the item price. The fifth column contains both the customer city and blank entries. The sixth column contains the customer state and blank entries. The data format for these columns is different.

Creating Base Tables

The first step to creating views to the non-normalized data is to create and define base CUSTOMER and ORDER tables, as follows:

  1. In the tree view, add the CUSTOMER and ORDER tables.

  2. Click the General Table properties page for each table and define its properties.

  3. In the tree view, add columns to the tables, as follows:

    • For the CUSTOMER table, add the COMPANY_CODE, ORDER_NUM, NAME, ADDRESS, CITY, and STATE columns.

    • For the ORDER table, add the COMPANY_CODE, ORDER_NUM, PRODUCT, and PRICE columns

  4. Click the General Column properties page for each column and define its properties.

Although each base table points to the same physical file, the BASIS ODBC Driver cannot be used to perform queries against CUSTOMER and ORDER tables because each has a different column type and different numbers of columns. It is possible, however, to define two new relational views to be created to filter out the unneeded records.