In message <[log in to unmask]>, Dave Diaz
<[log in to unmask]> writes
>I am quite familiar with SQL but new to IMAGE. One common data
>structuring technique in SQL is the use of a 'secondary index' on a
>table to provide fast access by an alternative access path from the
>primary index, as in ;
>
>create table employee (
>        empno           number (5),
>        name            char (30),
>        dept            char (4)
>)
>create unique index empix on employee (number);
>create index empdep on employee (dept);
>
>create table department (
>        dept            char (4),
>        depname char (30),
>        manager number (5)
>)
>create unique index deptix on department (dept);
>
>This allows all employees of a specific department to be retrieved efficiently.
>
>What is the best / recommended way to structure this data in IMAGE. If
>I have 2 master sets EMPLOYEE and DEPARTMENT I don't think I can make
>EMPLOYEE a detail of DEPARTMENT, so I must create a detail set say
>DEPTEMP which has items DEPT and EMPNO which is a detail of DEPARTMENT
>chained on DEPT and containing the employee number (or record number?)
>of the EMPLOYEE master.
>
>Have I got this right or have I missed something ?

It'll work, and sometimes such an intermediate 'keyholding' detail set
is unavoidable. But it's very rare.

However, if I were you, I wouldn't start from here :-)

It is seldom a good idea to create Image Master datasets, except maybe
for codes and other unique non-linked items.

Three reasons: (i) the sort of thing you have found above; (ii) you have
to lock the entire dataset when adding or deleting entries - you can't
use a record level lock; (iii) you may have to deal with migrating
secondaries[1].

You will not find this advice in the Image manual :-(

Instead, create your datasets as Detail datasets and use Automatic
Masters to hold the ends of your key chains (so to speak).

So above, you would have an Automatic Master Employee-Auto, with empno
in it, linked to the detail set Employee.

The automatic master is transparently maintained by Image (as its name
implies) as you add and delete records from Employee. The purpose of the
auto-master is to replace the keyed access to Employee which you would
otherwise lose when making Employee a detail dataset.

The other auto-master to create is then Dept-Auto, with dept in it,
linked to the detail set Department.

This gets you back where you were with the two Master sets.

But what you can now, do to go forward, is *also* to link Dept-Auto to
dept in Employee.

This enables you to retrieve all employees for a given Department, which
is your wish.

If you make this link a sorted chain, you even get the employees
returned in empno sequence.

As with auto-masters, you don't have to do anything specific to maintain
such chains, even sorted ones - just add or delete employees, and Image
will take care of it all for you, by dint of the database structure you
have put in place.

Downside to using Details as above? Using a Master enforces uniqueness
of key - you can't have two identical empnos in Employee, nor two
identical depts in Department. With Details, you have to program for
that. But you would anyway, wouldn't you, to give your users friendly
messages, not Image errors?

Masters also enforce referential integrity - in the two masters/one
detail structure you first thought of, you wouldn't be able to delete a
Department record if it still had employees in it. In the two
details/two autos setup, you could.

But again, you'd program for this - Image lets you ask how many
employees in the department chain, a number it knows without having to
read the whole chain - and you can just say 'no' to an attempted
Department record delete if this number is non-zero.


I haven't gone into the technical details of how you do all this, as
once you know the approach you want to take, all the details are in the
Image manual, and described much better there that I could here.....

...but the trick, as you surmise, is knowing what approach to take.


[1] Migrating secondaries are the 'fourth bear' of Image (look up the
'Three Bears of Image' on the 'net to see what they are!) In a master
set, whenever a record being added hashes to the same address as an
existing record, it is stored in a 'secondary' location. That's OK; it's
transparent to you at that point, and on retrieval and/or update of
either record.

But if you *delete* the primary record, the secondary migrates into its
slot.

Every first-time Image user has, I imagine, written a program to delete
all the entries in a Master set, reading it serially, and been
bewildered to find it not empty when they have finished.

Reason? Delete a record and just move on to the next, and maybe a
secondary will have snuck into the location of the record you just
deleted. There's a solution; keep deleting each location until it's
empty.

But there's a better solution; avoid using Master datasets where
possible.
--
Roy Brown        'Have nothing in your houses that you do not know to be
Kelmscott Ltd     useful, or believe to be beautiful'  William Morris

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *