Title: ODBC Planning

Issue: Spectrum Jul/Aug '98

author:Nathan Rector

company:Natec Systems

email:nater@northcoast.com

http:www.northcoast.com/~nater/

In the last few articles I've covered how SQL files are setup and how to retrieve information, but I've yet covered the planning that is needed to make ODBC work. When working ODBC you have to decide what fields will be available through ODBC. Then you have to make MultiValue data conform to a "Normalized" structure.

This task is fairly simple, but it can take a lot of time trying to get things right. Here are a few steps that will help you plan for the use of ODBC on your MultiValue system. If you don't take the time to plan your ODBC connection before you try setting it up, you are likely to end up redoing your work 2 or 3 times before you are satisfied.

Step 1: Choose the fields you want access to ODBC

There are a few advantages that MultiValue environments have over most databases that use ODBC. You can hide specific fields from the users using ODBC as a connection medium. For example, you want to give your users the ability to use ODBC with your employee file, but you don't what these people to access the payroll wages or other private information. You may only want to give users access to the employee names and addresses and that is it. You are able to do this when using ODBC with MultiValue databases.

Planning the interface to the database will be the most challenging part of setting up ODBC. To illustrate what I'm talking about, let's look at a simple database. The database has a CUSTOMER, ORDER, and EMPLOYEE file.

CUSTOMER

------------

CUST.NO

ACCT.NAME

ADDRESS

CITY

STATE

ZIP

PHONE

FAX

SALESPER.NO

ORDER

------------

ORDER.NO

CUST.NO

PART.NO

QTY

DATE.COMP

DATE.SHIP

EMPLOYEE

-----------

EMP.NO

FIRST.NAME

LAST.NAME

ADDRESS

CITY

STATE

ZIP

COMMISSION

HOUR.DOL

Keep in mind when you are defining the fields to be accessed that all of them you define will be accessible by anyone with the appropriate passwords and ODBC programs. This can cause problems if you want to give both Accounting and Sales access to the employee file. Sales may want access to the employee file to get the salesperson's name on reports. Accounting may want the dollars per hour or commission rates for their spreadsheets.

If you want to do something like this, you may have to setup a total separate account for the users to login under; define q-pointers to the needed files; then map the files in this account. This process is long and confusing. For simplicity and easier management of ODBC tables, try to avoid the need for this.

Step 2: Separate single value fields from multi-value fields

Since ODBC uses SQL as the database structure, you must normalize your data to work with that structure. Normalizion is a process of making your database flat, or creating a 2-dimensional file structure. Since a MultiValue database inherently has multi-value information in it, you must separate the single values from the multi-values.

Please keep in mind, we are still in the planning stage and are not actually moving the data or altering your current data structure. You do not need to actually change anything about your current data structure. The programs that supply ODBC support to multi-value data, map your fields into a logical format. The current data structure will never change.

Once you map your fields as single values, you need to remap the fields if they become multi-valued later. For example, if a phone number field starts out as a single, but later becomes a multi-value field, you will have to re-map this field. In addition, any program that uses ODBC and this field as a single value has to be changed as well.

Step 3: Define the associations for the multi-value fields.

Once you have decide which multi-value fields will be accessible through ODBC, you must define the associations. Let's look at the customer file in the example database (figure 1). There are two fields that are multi-valued but are not associated: PHONE and FAX. Make these as separate associations.

Now let's look at the Order file. The fields PART.NO, QTY, DATE.COMP and DATE.SHIP are multi-valued fields that are associated together. Mark these fields are associated.

The ODBC server program that is used to access your database creates the needed tables automatically for each association.

Step 4: Decide if your multi-value fields need to stay in the same order

When accessing multi-value information through ODBC there is no guaranty that when your information is saved it will be in the same order that it was originally saved in the database. If you want to make sure that multi-value line 5 stay multi-value line 5, then you must include a virtual field that supplies the multi-value line number.

Step 5: Map the fields

Once you have decided which fields to display and which fields are single and multi-valued, it is time to map the fields. The software you are using to access your database through ODBC dictates the exact method you use to map your fields.

The example that follows is using D3/NT as the database. D3/NT comes with ODBC connectivity without any extra costs involved. There are other methods and tools available for the other MultiValue databases such as ViaODBC from Via Systems or Liberty ODBC.

:SQL-CREATE-TABLE CUSTOMER CUST.NO ACCT.NAME ADDRESS CITY STATE ZIP [PHONE] [FAX] SALESPER.NO (C

:SQL-CREATE-TABLE ORDER ORDER.NO CUST.NO [PART.NO QTY DATE.COMP DATE.SHIP] (C

:SQL-CREATE-TABLE EMPLOYEE EMP.NO FIRST.NAME LAST.NAME ADDRESS CITY STATE ZIP (C

You'll notice that I left COMMISSION and HOUR.DOL off the Employee file configuration. This keeps people from accessing this information through ODBC.

Step 6: Test the ODBC configuration

The last step is TESTING! Always, always test your ODBC setup. If fields are set up incorrectly, users will get undesirable results when they try to access the data. Also, it is really easy to miss key fields that are needed to access the information correctly. Testing helps identify any problems before your users do!