Storing reverse relation (or internal relation) metadata in a separate database table
Contents
A reverse relation implies that many-to-many links between records from (usually) different database tables are possible. For example: you can link a single object record to multiple exhibition records (because the object appears or has appeared in multiple exhibitions), while an exhibition record can link to multiple object records (because an exhibition can obviously display many objects). In some cases you may also like to store data about the relation itself, metadata that is not specific to either record in the reverse link but to their particular relation. For the reverse relation between an object and an exhibition for example, you may want to be able to register the display period applicable to this relation, especially if that period differs from exhibition period itself. Possibly the object will even be on display during more than one specific period in the exhibition. So then you'd like to be able to link the same exhibition record as many times to the object record and register the period metadata per link. Something similar would apply to a reverse relation between objects and books where you'd like to register page reference metadata somewhere, as long as it's not in the object or the book record.
Internal relations are links between records in the same database table, like a parent record which links to multiple child records. Here too, you would sometimes like to store details about the relation itself as metadata.
That possibility is available in Axiell Collections but must be set up per desired reverse or internal relation in Axiell Designer 7.6.18093.4 or higher. Amongst your normal database table structure files (.inf files) you'll create a special metadata database table (which will contain records with the metadata per relation) and an equally special, so-called triple index which will associate the linked record numbers from the reversely linked database tables to the record number of a relevant metadata record. The metadata database table won't get its own data source and screens. You'll have to add fields to the reversely or internally linked database table structures and to relevant screens for data sources associated with the linked database tables, so that when a link is displayed (a link from an object to an exhibition for example), the relation metadata can be retrieved automatically and be displayed too. Such merged-in metadata can also be edited, as it must be created from such fields as well. In fact, a new metadata record will be created once a reverse or internal link between two records is registered and this record will remain associated with this link (via the triple index) as long as the link exists - on deletion of a link, the metadata record will currently not be removed - so even when a user changes earlier entered metadata for a reverse or internal link or empties the metadata field(s), the link will still be associated with the originally created metadata record and any changes to the contents of the metadata fields will simply be processed in that metadata record. Once (after editing) a record with prior reverse links (from before you set up the metadata functionality) is saved, new metadata records will automatically be created for all those existing links, even though they do not contain any metadata (yet).
You can also search the contents of metadata fields.
It is recommended to implement an indexed link table structure for the relevant reversely linked fields too (before or after the implementation below), because that table improves performance and contains more data (for possible debugging): the indexed link table will then also contain the reference to the metadata record, so that table will then be used by Collections instead of the triple index created by the steps below (which you'll still have to perform).
Proceed as follows:
| 1. | First we'll have to create a metadata database table definition and its table in the SQL database. (You have to have dbo rights to the SQL database to proceed.) Although strictly speaking you'll only have to do this once, to have that table contain metadata records for any relation between any two normal database tables you care to create now or later, we recommend creating a separate metadata database definition (each with a different name) for each relation for which you'd like to be able to register metadata because metadata records do not contain a reference to the relation it originated from and if something were to go wrong with the contents of the single metadata database it'll be hard to research the cause of the problem. For now, for the single metadata relation we are creating, simply right-click the data folder in your Axiell system in the Designer Application browser and select New > Metadata database definition in the pop-up menus. |
| 2. | You'll be asked to enter the database name: enter metadata, for example, or metadata1 or a name which includes the linked database names or tags, if you think more metadata relations will require their own metadata table later on. After clicking OK, the new database definition will become visible at the bottom of the list of database definitions and the relevant table will have been created in your SQL database automatically. (The SQL storage structure of this metadata table is the same as that of normal database tables, only the record access table and pointer files (aka saved searches) support tables currently have no use.) Note that "clearing" this database via the right-click pop-up menu would also create the table. From Designer 7.9 and Collections 1.15, it is possible to journal data changes in metadata tables. On the Metadata database properties tab of a metadata .inf you'll see an Enable journal checkbox. Check it and next time you add or change record data in metadata fields, those changes will be registered in the automatically created new journal table in the SQL database: dbo.metadata_journal for example if you have a metadata.inf. For now, Collections does not have a user interface to view this journal yet so you'll have to query the table in SQL Server Management Studio to find changes you are looking for. |
| 3. | The database definition should get fields and indexes, but it can't have datasets, internal links or feedback links etc. Identify which reverse or internal link you'd like to enhance with metadata storage. Let's continue reverse with the object-exhibition relation as our example. Also think about which relation metadata you'd like to be able to register. Let's say we need a start and end date to mark a display period. We'll have to create these fields in both reversely linked database definitions, collect and exhibit in this case, and in the metadata database definition. Even though we'll create normal fields in collect and exhibit, data entered by the user will not be stored there, it will only be stored in the relevant fields in the metadata database table. Provide sensible field names:  In collect we create similar, repeatable fields which will serve as target fields in the linked field mapping, merged in from the metadata database table:  In exhibit we also create similar, repeatable fields which will serve as target fields in the linked field mapping, merged in from the metadata database table:  In both normal database table definitions then also create a repeatable Integer field, named metadata_collect_exhibit_reference for example. This field will contain the record number of a referenced metadata record. (This field doesn't require an index in these database table definitions.) The fields in the normal database table definitions should be part of the same field group as the linked field to which the metadata pertains. |
| 4. | Now edit both reversely linked fields of this example in the normal database table definitions, in collect and exhibit in this example. First mark the Meta data enabled checkbox for the exhibition field in collect. This will automatically mark the same checkbox in the reversely linked field in the other database table definition as well when you're finished with this step, object.object_number in exhibit in this case. Note that if you were to do a similar setup for internally linked fields, you would have to enter a Backward reference (the forward reference of the other linked field in the relation) for both internally linked fields (something you wouldn't do normally) to enable the Meta data enbabled checkbox to allow you to mark it. The superfluous backward references would then be ignored by the software so that the internal link could still function normally.  It will also add an extra properties tab to these two fields: Meta data properties. For both reversely linked fields you'll have to fill in this tab. For the Meta data database property, select the metadata.link (or other appropriate <my_dbname>.link) file from the browser window. In Meta data reference, select the reference field you created in the previous step. Then fill in the field mapping from the metadata source fields to either the collect or exhibit destination fields that you also created in the previous step.   Saving the changed database definitions will now create a special table in the SQL database, which represents a so-called triple index, a new type of index which links three record numbers to each other: the record numbers of the reversely linked records and the record number of the associated metadata record. (In the SQL database, this table is automatically named after the three involved database table definitions, the two link reference field tags of the linked fields in the reciprocally linked database tables and ends with _linkTable, in this case the name would be dbo.collectl2_metadata_exhibitlk_linkTable. All you really need to know about it is that it will be updated along with the normal link reference index tables for the reverse link as metadata records are added to the dbo.metadata (or differently named metadata) database table. Note that in this case that "clearing" the collect database table via the right-click pop-up menu would also create the triple index table. |
| 5. | Now add the start and end date fields as repeatable, writeable fields to the screens that present the reverse link, in our example the links to exhibit and collect: exhibit.fmt and objlistex.fmt:  |
| 6. | Save your work and recycle the Collections application pool: metadata can now be added to reverse links of this type in existing and new records. |
Note that the marked Metadata enabled option for a field and its Meta data properties will break compatibility for the relevant .inf with Axiell Collections older than 1.0.6673.28957. Removing the settings will restore compatibility with older versions although the extra SQL tables will remain present in the database table. Do not set up the metadata database table functionality if you are (also) still using the now deprecated Adlib for Windows.
Searching metadata fields
You may create access points in the reversely linked data sources to allow searching on the metadata fields. The principle is the same as for access points on normal merged-in fields (so you are not allowed to create local indexes on such fields). Proceed as follows:
| 1. | Create appropriate indexes for the metadata fields in your metadata database table. In our example that would be two ISO Date indexes. Reindex the new indexes after creating them. |
| 2. | Create relevant access points in the appropriate data sources in the application structure: in object catalogues and/or the Exhibition data source in our example. Let's create two separate access points in the Internal object catalogue. As the Search key, look up the relevant metadata field in the current database structure: |
| 3. | Save your changes and recycle the Collections application pool. When in Collections you now open the Standard search tab in the Internal object catalogue, you'll first have to click the Settings button to select your new access points and display them on the Standard tab. You should now be able to search the contents of your metadata fields. |
The searching part of the metadata database table functionality can be implemented in Designer version 7.6.18109.2 and higher, and used in Axiell Collections from version 1.0.18109.2.