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 *