Database Management

This invention relates to the construction and management of databases and in particular to a system for database updating.

A problem commonly experienced with large databases is that of updating with new or revised information. A specific example of a large database is that employed by British Telecom in the provision of their `directory enquiries` service. That database contains details of telephone subscribers and is accessed by an operator to retrieve subscriber information in response to requests from users of the system. This database is of course not static. New telephone subscribers are connected to the system while others may be disconnected for a variety of reasons. Subscribers may change their address (and sometimes their telephone number). Also, in the case of business subscribers, changes of name are not unusual. There is thus a steady flow of new information which requires updating of the database. The sheer size of the database has made this updating a difficult problem. Using current techniques, a day's work of updates takes more than a day to enter. Fortunately the amount of work involved is not directly proportional to the number of entries and if enough changes are saved before the updating process is run, it is possible to keep up with the rate of changes albeit at a cost of being always out of date. In practice, the largest (London) segment of the database is updated once a fortnight while the other segments are updated once a week. The updating process is very hungry both in processor power and disc space and a significant percentage of the installed equipment is in fact devoted entirely to updating and is thus unavailable for operator searching.

The object of the present invention is to minimise or to overcome this disadvantage.

According to the invention there is provided a database arrangement incorporating index records stored in a predetermined order, and listing records, each index record providing a pointer to a said listing record, and wherein the index records are stored together with distributed free space whereby to permit the insertion of new index records, and wherein each index record is provided with a radix sort key associated with a primary keyword in the corresponding listing record whereby each new index record may be inserted in the correct order without reference to the listing record.

An embodiment of the invention will now be described with reference to the accompanying drawings in which:

FIG. 1 is a schematic showing the structure of a conventional database storage arrangement;

FIG. 2 is a schematic diagram of a database storage arrangement according to the invention;

FIGS. 3a and 3b illustrate the structure of an index record in the database of FIG. 2 for a name index and a phonetic index respectively;

FIG. 4 illustrates the relationship between a set of listing records and corresponding index records in the database of FIG. 2;

FIG. 5 illustrates the file layout in the database of FIG. 2; and

FIG. 6 illustrates the format of a group of index records.

Referring to FIG. 1, which is introduced for comparative purposes, this shows a conventional database storage arrangement which may e.g. comprise a listing of information relating to telephone subscribers. In the arrangement of FIG. 1, information is stored in the form of index records of equal length and arranged in a predetermined order, and listing records of variable length. Each index record provides a pointer to a listing record. In this arrangement one or more index records may point to the same listing record.

Updating of the database of FIG. 1 requires the insertion of new index records at the correct point. To effect this insertion it is necessary for the entire database to be re-ordered, re-indexed and re-written. As discussed above, this is costly both in terms of time and processor power.

Referring now to FIG. 2, this shows in highly schematic form a database storage arrangement according to the invention. As can be seen from FIG. 2, the index records and the listing records are stored each together with a number of empty spaces to allow for insertions. The number of spaces may be determined by the projected future expansion of the database. The index records are stored in display order as their relative position determined the order in which listing records are displayed.. When the database is first constructed the listing records are ordered, but these records will gradually become disordered as changes accumulate. This is not a problem as the ordered index records provide the necessary pointers.

The format of index records in the database arrangement of FIG. 2 is illustrated in FIG. 3a which shows a name index record and FIG. 3b which shows a phonetic index record. The acronyms denoting the various parts of the index records are listed in Table 1 below.

The radix immediately precedes the RECID field unless the radix flag field is preset in the index record.

The RADIX element of the index record is a radix or numbering system that uses a common sod or prefix for associated numbers. Preferably the index is an extensible binary prefix. It is this radix which determines the insertion order of the new index record. In effect, the radix extends the ordinality property of numbers so that numerous insertions may be made between any other pair of radices to allow for updates. Radix insertions maintain the ordinality of the assigned values in a monotonically increasing order. To extend the range of assigned radices, significant binary digits are appended to the prefix. This extension may be made locally in the assigned range allowing for insertions at arbitrary points. In a preferred method, we compute the number of radices to be assigned when creating the index file. This range is distributed evenly across the entire index by adjusting the binary representation of the set of those numbers to the most significant bits of the used precision. The number n of binary digits required to represent the original prefix is chosen such that

For example, if the used precision is one byte and the original number of primary indices is 7, then counting from 1, to allow insertion prior to the original range, the assigned radices are those listed below in Table 2.

One byte Radix

001,00000

010,00000

011,00000

100,00000

101,00000

110,00000

111,00000

In Table 2 the prefix of each radix is indicated by a comma, and the trailing zeros provide the additional precision that allows insertions to effect in place updating. To create a new radix that lies between its nearest neighbours, the value is computed by a binary division of the sum of its immediate neighbour, i.e.

Referring now to FIG. 4, this illustrates the relationship between listing records and index records. The listing records shown relate to a typical business subscriber (English Metal Company Limited) having a number of different offices/branches. These records are accessed via corresponding index records assigned to the primary and secondary keywords.

Updating the database of FIG. 2 requires the operation, delete and insert (a modification can be treated as a delete followed by an insert). Deletion is effected by loading the appropriate index and listing records by the normal search procedure and writing over them with a deleted indicator. The deleted records this provide further free space. To effect an insertion it is necessary to find the current place to insert a new index record in the index. This may be achieved by the following steps.

1. Create a new listing record and insert it into a free space.

2. Create a new index record that points to the listing record.

3. Assign a radix to the index record based on the position in display order of the index record generated for the listing record's primary keyword.

4. Inserting the index record at a position determined by its radix.

The insertion point of an index record containing a listing record's primary keyword (TETRA) is found by reference to the listing records. Once the index records insertion point has been found it is assigned a radix between the radices of the index records on either side of the insertion point. The same radix is then assigned to all the other (secondary keyword) index records generated by the listing. The secondary index records are inserted by reference to the radices in the existing index records without the need to consult the listing records.

When index sorting in the database of FIG. 2, allowance must be made for the presence of alliterated index records. The alliterative flag is set only for those indices that are alliterations of the primary search key TETRA. This occurs for alliterated keywords of an SL listing and for the members of caption sets when they inherit the primary second key of the set header. In addition they may have their own finding names that are also alliterated to the primary key. In all cases the index records have the same radix. The index with the primary radix flag is sorted first. The order of the non-primary but alliterative indices is not important within their own grouping and follows the normal sort number, but this grouping should be arranged to follow on contiguously from the primary index. The above primary-first sort order is determined by a comparison performed immediately after the TETRA and radix have been compared equal, but prior to any further field comparisons. This ensures, e.g. that indices for SMITHSON-SMITH sorts the primary SMITHSON before the alliteration SMITH.

The records of the index files are sorted into a key and display order. The key is essentially the name or phonetic TETRA, that ensures all records with the same TETRA are grouped together in the index file, and those groups are sorted essentially into alphabetical order. Within any TETRA group, the index records are sorted into display order. Display order is determined by the contents of selected fields in the original listing records, and sort rules that may be applied to those fields.

Because of the ordering by key, the display order, and the uniqueness of the primary indices, the primary indices in the entire index file, i.e. belonging to all TETRA groups, are also in display order. The primary indices in each group are contiguous--bar alliterations--but may be preceded or followed by the secondary indices of listings with primary indices in preceding or following TETRA groups respectively. The overall file layout is illustrated schematically in FIG. 5 of the accompanying drawings wherein:

group-x--represents all index records with same TETRA key

p--represents primary (and possible alliterations--not shown) index

s--represents secondary index

and the selection of indices in rows 1 and 2 are both in display order.

An example of a TETRA key group of index records, for the TETRA "suit", showing the primary, alliterative and header markings and typical radix values is shown in FIG. 6 of the drawings. Referring to FIG. 6, the group begins with secondary indices which are associated with primary indices from earlier key groups. Hence they have low radix values. Note that the secondaries have no special markings. The next section of the group is the primary indices, for names of whose primary search key is the TETRA of the group, and they are marked as primaries. Generally, these indices will be sorted in the display order, and the TETRA and extension will follow that pattern. However, the pattern of the TETRA and extension can be broken by alliterations, although display order is maintained. The alliteration "SMITH-SMITHERS" shows that the first index is marked as a primary and an alliteration. It is immediately followed by its alliterative secondary index, which has the same Radix, 1050, and therefore sorts adjacent to it, but the primary must come first. Thus a literal search for `SMITH` or `SMITHERS` will extract the listing once in the correct display order.

A particular example is the set `SMITHSON SMITH INC`, which is alliterative, and the primary should follow alphabetically the alliterative secondary. However the primary is placed first. Moreover, each member is shown twice, since it inherits both alliterative keywords from the set header. The member indices are marked alliterative because they appear in the primary insertion point of the set, not just because the keywords of the set header are alliterative. All the indices of the set have the same radish. Those indices that refer to headers are marked as such only where they inherit the primary search key. The group ends with secondary indices which are associated with primary indices from later key groups. Since radices are assigned to primary indices which are maintained in display order, the radix determines display order for the secondary indices, and therefore further sort information is not required to locate the position of these indices.

The database arrangement of FIG. 2 is built up by the following sequence.

The Index files are built in three stages. These separate stages are desirable because of constraints on memory, for efficiency, and compatibility with existing software and the logical sequence of the algorithm. The first stage, wp.sub.-- ixsort, repeatedly does the following until the listings are exhausted; builds all the indices for each listing record, sorts the indices for (1000) listings in memory, and writes out that `sorted run` into a temporary database file. O(1000) sorted runs are produced from each listings file. The name and phonetic temporary files for business or residential listings are built together at this stage.

The second stage, wp.sub.-- ixradgen, is based on a merged sort process, that takes as input the sorted runs of names indices of the first stage, and generates a table. This table contains an entry for each primary index: a listing record produces one index record that is termed the primary index, because it is an index for the primary search keyword, i.e. the first word of a surname or business name for that listing. Any other index records generated by that listing are termed secondary indices and are built from subsequent keywords. The second stage determines the display order of the primary indices for names, and records the information in the table.

The third stage, wp.sub.-- ixmerge performs the full merged sort on the sorted runs, and writes the index records into an index file. This stage uses the table from the second stage to generate and assign radices to both primary and secondary indices as they are written. The temporary file and table may be discarded after this stage.

To create the full complement of database index files, the first and second stage are run twice, once for business and once for residential listings. The third stage is run four times, once for each of the database index files; name and phonetic for both residential and business.

The radix sort key is introduced into the index record by enhancing the index build programs, as described below.

The first stage of index build is the wp.sub.-- ixsort process. Wp.sub.-- ixsort produces the sorted runs of indices and writes them to a temporary database.

During wp-ixsort, for each straight line, SLU header or caption set header record processed, a unique number is generated, a radix index. The same number is given to each index record associated with that listing record, both name and phonetic, and in the case of caption set headers, its constituent members' indices. The radix index may be a simple incrementing count, assigned in the order of arrival of the listings, and placed in the radix field to be overwritten by the later stage of radix generation. The primary and any alliterative and header indices will be marked as described earlier.

On completion, wp.sub.-- ixsort creates a set of sorted runs of index records, where within each run :the index records were sorted in display order, and each primary index record would contain a unique radix index. As an example, if we had processed the following listing records in order:

this would produce for the first and second names:

In order to generate the radix sort key, a process wp.sub.-- ixradgen is inserted prior to running wp.sub.-- ixmerge. This process, wp-ixradgen, performs a merged sort of the sorted runs to deduce the display order of the primary indices as they would appear when the name index file is completed. The process creates a table, indexed by the radix index assigned by wp.sub.-- ixsort, to map these primary indices into their true ordinal position. Hence, all the runs are scanned simultaneously to assess the merged order, and each time a primary index is encountered, its ordinal position is placed in the table against the radish index that primary contains

Thus for the example above we have:

This table is written into the temporary database for use by the next stage of the process.

The final stage in the generation of the radix sort key is to run an enhanced wp.sub.-- ixmerge process, which produces the final sorted index files, replacing each radix index number with its associated radix from the table produced by wp.sub.-- ixradgen.

By reading the entire mapping table, the number of entries and hence the range of radices may be assessed. The ordinal position in the table may then be normalised into a radix by the process described in the section on radices. The generated radix would then overwrite the radix index in the index records before they are put in the index file. The radix index mapping table may be used to generate the same radices for the name and phonetic index files. The final name index file is shown in the following table for the example described above with a radix of single byte precision: