From 15b74d8fe455549b61c9763d8870a2425539bb51 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <kop@karlpinc.com>
Date: Tue, 30 May 2023 17:49:57 -0500
Subject: [PATCH] More documentation on the system's design and correct use
---
doc/src/architecture.m4 | 1 +
doc/src/architecture/special_values.m4 | 72 ++++++++++++++++++++++++++
doc/src/er_diagrams.m4 | 35 +++++++++++++
doc/src/intro/about_db.m4 | 26 +++++++++-
include/constants.m4 | 12 +++++
5 files changed, 144 insertions(+), 2 deletions(-)
create mode 100644 doc/src/architecture/special_values.m4
diff --git a/doc/src/architecture.m4 b/doc/src/architecture.m4
index d475b1a..5867e71 100644
--- a/doc/src/architecture.m4
+++ b/doc/src/architecture.m4
@@ -27,5 +27,6 @@ System Architecture
:maxdepth: 3
architecture/databases.rst
+ architecture/special_values.rst
architecture/users.rst
architecture/schemas.rst
diff --git a/doc/src/architecture/special_values.m4 b/doc/src/architecture/special_values.m4
new file mode 100644
index 0000000..0d4b709
--- /dev/null
+++ b/doc/src/architecture/special_values.m4
@@ -0,0 +1,72 @@
+.. Copyright (C) 2023 The Meme Factory, Inc. www.karlpinc.com
+
+ This program is free software: you can redistribute it and/or modify
+ it under the terms of the GNU Affero General Public License as
+ published by the Free Software Foundation, either version 3 of the
+ License, or (at your option) any later version.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU Affero General Public License for more details.
+
+ You should have received a copy of the GNU Affero General Public License
+ along with this program. If not, see <https://www.gnu.org/licenses/>.
+
+.. M4 setup
+include(constants.m4)
+include(macros.m4)
+sdb_rst_quotes(`on')
+sdb_generated_rst()
+
+
+.. special_values:
+
+Special Data Values
+-------------------
+
+As much as possible SokweDB utilizes a controlled vocabulary within
+the system's data store. To provide the system's users\ [#f10]_ with
+control over the codes used, this vocabulary may be tailored by adding
+or deleting codes to or from the tables which define the system's
+vocabulary.
+
+At times, SokweDB recognizes that particular codes have special
+meanings, for example, the BIOGRAPHY_ table's ``sdb_female`` (female)
+Sex code. The meaning of these codes is fixed into the logic of the
+system. As examples, an individual must be female to be allowed to
+have a menstruation, or, the individual must be in the community to be
+sighted in the community. Some of these codes, like sex, are not
+defined in tables, they are hardcoded into the system. Others are
+defined in support or other tables. Because these codes have intrinsic
+meaning, they cannot be removed from the SokweDB system nor should
+their presence in the data be used to code a different meaning from
+that which the code presently has. For example, the meaning of
+DEPARTTYPES_ code value ``sdb_end_of_obs`` (alive\ [#f11]_) should not
+be changed to mean âdeath due to meteorite impactâ because the
+system's programs would then allow individuals to have sexual cycles
+after death. Each of the "special" values that the system requires
+retain particular meaning is listed in the Special Values section of
+the code table's documentation. For further information on the meaning
+of the "special" values, see the description of the table(s) that
+contain the code values. Should the meaning of one of these "special"
+values need to be changed, the logic in the SokweDB programs should be
+adjusted to reflect the change.
+
+SokweDB prevents ordinary users from altering rows that contain
+special values in an attempt to prevent mis-configuration of the
+system. Only users with permissions to modify a table's triggers may
+alter the table's special values. This is not a panacea. To return to
+the example above, not only does the system expect a DEPARTTYPES_ code
+of ``sdb_end_of_obs`` to mean alive, it also expects
+``sdb_end_of_obs`` to be the only code in DEPARTTYPES_ that means
+alive. If another DEPARTTYPES_ code is created to indicate a more
+specific sort of "alive-ness", unless re-programmed the system will
+consider all individuals given that code to be dead, not alive. A
+careful review of the documentation should be undertaken before
+modifying the content of tables that instantiate special values.
+
+.. rubric:: Footnotes
+
+.. [#f10] As opposed to the system's programmers.
+.. [#f11] Specifically, "still alive and present as of the last census date".
diff --git a/doc/src/er_diagrams.m4 b/doc/src/er_diagrams.m4
index 93e6502..32aac0f 100644
--- a/doc/src/er_diagrams.m4
+++ b/doc/src/er_diagrams.m4
@@ -30,6 +30,36 @@ of how rows in the database's tables relate to other rows. They are
dense in information about what data exists and in what tables it can
be found.
+Most tables have have an id, or key, column that contains a number
+unique to that row within its table. The id can be used, in
+perpetuity, to refer to its related row and distinguish it from all
+the other rows of the table. Ids are arbitrary, although for
+convenience they are often sequentially generated integers. The name
+of the column holding the id value is not always Id, although it
+sometimes is.
+
+A relationship is established between the rows of two tables when an
+id value from one table appears as data in the other. The relationship
+notion is made most clear by way of diagrams and examples. The
+relationship concept is at the heart of relational databases and,
+while the underlying idea is rather simple, it took many years to
+develop relational database concepts so don't expect a full
+understanding immediately.
+
+When an id value of a row in one table appears as data in a second
+table, the data in the second table can be used to retrieve the
+identified row from the first table.\ [#f9]_ When an id value of a row
+in the first table appears as data only once in the second table, the
+two tables are said to have a one-to-one relationship. One row in the
+first table relates to one (or possibly zero) row(s) in the second
+table. When a row's id value can appear in more than one row of a
+second table, the two tables are said to have a one-to-many
+relationship. One row of the first table can be related to many rows
+in the second table. One-to-many relationships are more common than
+one-to-one relationships. In the ER diagrams each table (entity) is a
+box, and each box contains a list of the table's columns. The lines
+between the boxes represent the relationships between the tables.
+
For example, individuals can transfer between communities zero or more
times. A single individual is one kind of entity recorded in the
database, one transfer of a single individual between communities is a
@@ -49,3 +79,8 @@ individual transferring also exists.
:maxdepth: 3
er_diagrams/key.rst
+
+.. rubric:: Footnotes
+
+.. [#f9] And the reverse is true. The id of a row in the first table
+ can be used to find the row in the second table that holds it.
diff --git a/doc/src/intro/about_db.m4 b/doc/src/intro/about_db.m4
index f732539..20fd0b9 100644
--- a/doc/src/intro/about_db.m4
+++ b/doc/src/intro/about_db.m4
@@ -91,6 +91,7 @@ meters, and the id of the 2nd chimpanzee. Each column of a table is
expected to contain the same "kind" of data; a name should not go in
the "birthdate"column.
+
.. _views:
About Views
@@ -98,8 +99,29 @@ About Views
Views appear to be tables but they are not. Views are virtual, when
queried they deliver the results of a query run against the database's
-actual tables. When setup to do so, changing the data in a view can
-change data in the database's underlying tables.
+actual tables. An SQL query can freely intermix the use of tables and
+views. When setup to do so, changing the data in a view can change
+data in the database's underlying tables.
+
+Views make it easy to reuse complex or commonly used queries, or
+portions of queries. They allow a database designed around the
+capabilities of the computer to be interacted with in a fashion that
+makes sense to people. Although the views do not appear in the entity
+relationship diagrams that document the underlying database, and so
+are omitted from the high level overview these diagrams provide, most
+users will greatly benefit if they take the time to understand how the
+views fit into the overall database. Where views exist, most will
+usually find it easier to work with the views than with the underlying
+tables.
+
+Views that have the structure, the corresponding columns, of the data
+after collection in the field and entry into electronic form, are used
+to upload data into the database. Inserting data into these views
+distributes the uploaded data into the underlying tables. These sorts
+of views may or may not be useful when retrieving data from the
+database for analysis. Investigate to see if some other view or query
+is better suited rather than automatically using a view created for
+data upload to do analysis.
.. _about_schemas:
diff --git a/include/constants.m4 b/include/constants.m4
index 720fcd8..d9aa4a0 100644
--- a/include/constants.m4
+++ b/include/constants.m4
@@ -33,5 +33,17 @@ dnl
define(`sdb_reader', `reader')
define(`sdb_writer', `writer')
+dnl
+dnl Special values -- hardcoded constants
+dnl
+
+dnl Sex
+define(`sdb_male', `M')
+define(`sdb_female', `F')
+define(`sdb_unknown', `U')
+
+dnl Departtypes
+define(`sdb_end_of_obs', `O')
+
divert(`0')dnl Output with m4 again
]}])dnl End of ifdef over the whole file.
--
2.34.1