Schemas

Each SokweDB database contains a number of schemas. Some of these schemas will not be documented herein; it is likely that some schemas will be created to hold shared data, data not part of SokweDB itself but related to it.

The sokwedb schema

The sokwedb schema contains the data collected in the field. It is the primary schema of interest to the researcher.

The codes schema

The codes schema exists so that the sokwedb schema is not cluttered with un-interesting tables, and other things.[1]

The codes schema contains those tables which control the data vocabulary defining the codes able to be recorded in the database. Because the codes defined in this schema are often used and well-known the schema’s tables are not often of interest.

Most of the tables in this schema contain one row per defined code. The codes are usually kept in a column that has the name of table, but a name which is singular instead of plural. There is also a Description column, which describes the coded value.

A few of the tables in this schema are more complex, and contain more than 2 columns. These are often tables which contain ‘meta information’ involving the mechanics of the data collection process. Things like lists of researchers, observers, or equipment used in the data collection process.

The codes schema also contains functions and other tools used to administer the system.[2]

The upload schema

The upload schema contains the views used during the data upload process. These are, usually, of interest only to those who upload data into the database.

The lib schema

The lib schema (short for “library”) contains things used by SokweDB’s internal mechanisms. The end-user should not need to be concerned with its content.

The per-user private schemas

Individuals are given their own schemas in which they can do whatever they wish. So each regular account/login/username has an associated schema with a name the same as that of the account.

Caution

It is usually bad practice to grant another person access to something in a private schema. It is often better to create another, shared, schema. In this way individuals, and their accounts and private schemas, can come and go without affecting the work of the institution.

Because of the schema search order the schema name must be used to qualify anything created in the user’s schema. E.g., to create the table foo in the user mylogin’s schema:

CREATE TABLE mylogin.foo (somecolumn INTEGER);

Footnotes

[1]

The codes schema might be better named “administration”, but that name is too long to type.

[2]

Every data structure, think materialized view or other constructed table content, that is automatically constructed by the system’s triggers should always have, as an alternate approach, a way to be manually rebuilt. That way, should the triggers ever fail to operate properly, there is a way to recover. In addition, a second method provides a check against with both methods can be tested to be sure that the results are as expected.

Providing a function for every such constructed table, or other constructed data, that rebuilds the table, provides a way for the the reconstruction to be done manually.

Page last generated: 2025-04-12 18:47:18 UTC