Acumen Database Authorities

From UA Libraries Digital Services Planning and Documentation
Revision as of 11:30, 5 July 2017 by Jlderidder (talk | contribs)

There are three tables in the acumen/acumen_staging/acumen_dev databases that deal with fields such as titles, subjects, dates and such:

  • authority_type
  • authority
  • file

In authority_type, each field is assigned an id number:

  1. title
  2. creator
  3. archivist
  4. subject
  5. date

... and so on. The ID number assigned here is used to refer to the value stored in the authority table.

The authority table brings together ID values from the file table, the asset table, the authority_type table, and matches them to a value. So if you wanted a list of all titles, you could query: "select value from authority where authority_type_id = 1;" since in authority_type, title is assigned ID number 1. Normally, though, we want to find a value for a particular file.

The file table has multiple fields, some of which you will need if troublshooting Acumen. Of interest in this discussion are the file_name, such as "u0003_0000581_0000001.mods.xml" and the title field, which oddly enough echoes what's already stored in the authority table.

Let's say I want to see the title that is stored for u0003_0000581_0000001. I need to check two different places, and be sure to use the full file name. First: `select id, title from file where file_name like "u0003_0000581_0000001.mods.xml"`; That gives me the following result:

 | 150791 | Letter from Henry L. Abbot to William C. Gorgas, 1904-02-11 |

Now, I already know that title has ID of 1. So my next query is: `select value from authority where file_id=150791 and authority_type_id=1;`

 | Letter from Henry L. Abbot to William C. Gorgas, 1904-02-11 |

As you can see, the two values are the same. But should you have to modify them directly in the database, be sure to modify them both.

Now if I want ALL the authority fields for this record, I can use the ID from querying the file table:

`select authority_type_id, value from authority where file_id=150791;`

This gives me a result of 43 rows of information, but with an authority_type_id for each field so I can check the authority_type database to see how each field was indexed.