Relational Tables

Relational Tables and Databases were invented in the 1970s. They have a poweful and well-founded mathematical Basis and a high Maturity. They are used in every Sector of Information Technologie, from Mainframe down to Mobile Phone.

The Basics are easy to understand. Mastering them yields a better Understanding for any Software-System.

In Spoc-Web this Theory is seamlessly connected to the new Ideas of the semantic Web. This Combination of proven and novel Technologies yield a System, that is both extremely  flexible and highly performant.

Tables are 2-dimensional Data Stores, consisting of Columns and Rows.

Every Row represents an Object/ Thing with their own Identity.

The Columns each contain Data / Values of the same Type with same Meaning.

Allowed Data-Types are:

• Text
• Date and Time
• Numbers: whole Numbers ("integer") or Float-Point-Numbers
• References to Rows in relational Tables: usually as integerNumbers

Especially these References allow for the Flexibility and Power of the relational Modell and contribute to its great Success.

Rows and Columns form the so-called "Cells" at their Crossings. The Cell Content should be very "atomic", for both the User and the Database can process the "Inner" of Cells only with higher Effort. This means for Example, that You should create separate Columns/Cells for first and last Name, if You want to use them separately.

When describing Objects we explained, how important a unique Name is. The same is true for Tables: the unique Name is called "Key" there. Tables usually have at least two disjoint Keys: a technical ID (usually an integer Number counting up) and a unique Combination of Domain Columns.

Every Row should have a single, unique Name, that allows to distinguish if from all other Rows. A specific Column is created for this Purpose: the so-called "ID" or "Identity" Column. Usually an integer Number is created for this Purpose and counted up for each new Row. It is paramount, that this ID is NEVER changed. It must also NOT be re-used for other Rows, should the original Row be deleted. This is why a Number is so well suited, because there should be no Need to whimsily change a Number. Usually these Numbers are not published either, so there is no desire to change it too.
When these Numbers are published though, like Account Numbers, these are usually started with at least six digits, so that Customers don't ask to change them.

Apart from the technical Key there is at least one unique "natural Key" consisting of one or more Columns that allow to uniquely identify a Row. Because the technical Key is usually not published, the natural Key is needed to distinguish the Rows without resorting to the ID, using only publicly available Properties. With People a common Combination is: First and Last Name together with Birth Date and Place.

The Choice of Columns fundamentally depends on the Business Domain, especially on which Information is actually available. 

It shows that the Correspondence between relational Columns to the Classes described here is complete and NOT accidental. The following Table compares the structural Elements of relational Tables and the semantic Conzepts of Individuals and Collections.

Relational Databases always use several Tables to store their Data. This is different from simple, tabular Lists and Spreadsheet Applications that use only a single Table. References within these Tables correspond to Connections between the Row-Objects. These References allow to avoid Duplication of Data, reducing the Load, but even more important: modelling Reality better, because real- World Objects are never duplicated.

In the Example above not only the Adresses is repeated, but also the Family Name.

Introducing a new Table called "Family" eliminates repeating Data and reflects the Fact that Families usually live together at the same Address:

By Default, all Relations can be traced down to 1:N Types. Despite this, the following special Cases should be considered:

• A-1: B-0/1 Relations appear when Table B uses the Values from A.ID (like in 1:N) and additionally has a Uniqueness Constraint on its Column B.A_ID. B can now refer to each Row of A only once. On the other Side though there may be Rows in A, that are NOT referred by any Row from B. Now we have to discern between the following two Cases:
  
  • B.A_ID allows for 'null' Values or
  • B.A_ID must always be filled. In this Case A_ID can be chosen to be the primary Key for Table B. This Kind of Relation is often used to model Polymorphy in object-orientierted Programming.
• A-N : B-M Relations are realized by a Pair of Relations to a narrow Table P: Between Table A and B there is a Table P with only two Columns, containing Pairs of Values: A.ID and B.ID that point to the respective IDs of Table A and B. Since the Table P has only two Columns and no own Identity, it is often skipped during High-Level Modelling and the two 1:N and M:1 Relations are displayed as a single M:N Relation.
  Because there may be arbitrary many Pairs, any Combinations of Rows from A and B can be created.
  Examples are, among others, Participation of Studenten to Courses:
  
  • every Student can participate in several Courses
  • in every Course there will usually be several Studenten.

Pure N:M Relations are rather rare. Very often during Development more Data appears that need to be stored for the Relationship, like e.g. the Duration of the Participation in a Course. These Values can only be stored in Table P.

As soon as these additional Data also contain Relations to the Link Record, References to Rows of Table P must be enabled. So P receives a techncal ID Column, promoting it to a full Table/Class and giving its Rows unique Identities.

Spalten have a lot of Properties. Apart from the Data Type,  especially the Constraints determine the Role of the Column. These Constraints are described in the following Sections. 

Relational Databases have a special Value "NULL", which can be set to demonstrate that the Cell is not filled or the Value is unknown. NULL can be used in Columns of any Data Type. NULL is unequal to any other Value including itself, i.e. NULL <> NULL, different from Programming Languages. Comparing with NULL always yields "false", because You cannot tell with unknown Values. Actually this would require ternary Logic, that allows for NULL, in addition to true and false. Instead, SQL offers special Operators: IS NULL, IS NOT NULL and the Function isNull().

Not-Null-Constraints ensure, that there always is a Value entered into this Column. This is usually the Case for Key Columns and for Fields and Relations that are mandatory due to Business Reasons.

Unique Constraints make sure, that no duplicate Values are entered into the specified Column(s).

Uniqueness should be configured for every Key: 

• For the technical Key this ensures that References are unique.

• For the Business Keys this prevents Duplicates. 

According to the SQL Standards, You can define Unique Constraints with Nullable Columns. Rows with NULL-Values are exempt from Uniqueness, because comparing with NULL always yields false (see above). SQL Server (Microsoft) only allows for a single Row with NULL, except You activate SET ANSI_NULL ON. Alternatively You can define a "filtered Unique Constraint" that ignores Rows with NULL Values.

Eindeutigkeit sollte für jeden Schlüssel eingestellt werden, sowohl für den technischen wie auch für alle fachlichen Schlüssel. Gemäß SQL Standard kann man Unique Constraints auch auf Nullable Spalten definieren. SQL Server (Microsoft) erlaubt allerdings nur eine Zeile mit NULL, außer man aktiviert SET ANSI_NULL ON. Alternativ kann man dort auch einen "filtered Unique Constraint" setzen.

Foreign Key Constraints make sure, that References from this Column always exist. Defined on the Foreign Key Column, they only allow for NULL or Values, that exist in the referenced ID-Column.

Vice versa, Foreign Key Constraints also prevent accidental Deletion or ID-Modification of Rows while there are Cells pointing to them. Instead of preventing Deletion or Modification the followind Behaviors can be configured: