Classification of tables in relational databases on the basis of integrity and data redundancy

The content of the article

Justification of the article and some key concepts;
1. Reference books and bundles;
1.1. Types of tables;
1.2. Types of reference books;
1.3. Types of ligaments;
2. Generalization of the classification;
2.1. Classification in tabular form;
2.2. Classification in schematic form;
3. Some comments on the use of classification;
3.1. The use of classification in the normalization of tables;
Conclusion

Justification of the article and some key concepts

Very often attended the training discipline "Database". I studied once myself ... Somehow I even had to conduct a whole course for friends and acquaintances. During my studies, I noticed that difficulties arise already at the stage of understanding the tables and how to use them. Many simply could not and cannot develop simple databases. After a more detailed consideration of such a concept as a table and a small classification, the difficulties of perceiving tables in relational databases almost always disappear. So!

This article will consider a small classification of tables based on integrity and redundancy. What does it mean? This means that examples will be given describing what structure of tables you can do to prevent (try to prevent) redundancy and to achieve integrity in relational databases.

For understanding, we will give brief definitions of data integrity and redundancy:
')
Data integrity is a property of the ability to restore others by one data, without losing the semantic unity of this data and the relationship between them (between data).

Data redundancy is the state of the database, where extra data is present in the tables.

Data integrity may be affected by data modification operations. If deletion and update operations are prohibited in the database, the integrity can be broken only as a result of the add operation, as well as incorrectly written data display scripts.

1. Reference books and bundles

1.1. Types of tables

We delve a little into the small classification of tables by the types of their structure. We divide the tables into two general types. The first type will be reference tables, the second table-bundles.


Figure 1. References and links

Information in the tables can be divided into two types. On information that describes objects (subjects), connections and information that describes actions, processes, events, otherwise.

Reference books contain information about objects and subjects, connections. Bundles contain information about actions, processes, events, and so on.

Bundles store data taken from reference tables. Since it is unprofitable to repeat the same data when describing objects (subjects) and when describing their interaction, data about objects (subjects) are entered in directories, and tables-bundles do not store data of objects (subjects) in their pure form, but only links to them (foreign key). Thus, bundles store data on the interaction of objects (subjects) and references to the objects (subjects) themselves (foreign key). These "links" are primary keys in the tables of reference books. But more about that later ...

The reference book differs from a bundle in that reference tables can be independent and independent (that is, when reading the data of some reference books you can understand the semantics in general), and the link tables almost never.

1.2. Types of reference books

Directories can be divided into several types. These are static, static-dynamic and dynamic reference books. Of course, it is hardly possible to call an absolutely static reference book, since everything can change in this world. Or almost everything.

A static reference book is a reference book, data about objects, subjects, connections in which either are never subjected to modifications after the initial modification, or are so rarely subjected to modifications that this can be neglected.

An example of such directories can serve as a list of months with names and numbers, a list of days of the week, a list of seasons, a list of oceans, and so on ...

room	Name
one	January
2	February
3	March
four	April
five	May
6	June
7	July
eight	August
9	September
ten	October
eleven	November
12	December

Table 1. An example of static directories

Static-dynamic reference book is a reference book in which data on links are stored, if links are of a reference nature. In this directory may be foreign keys.

The most successful example would be a table with such medical data as weight. The list of people whose weight is measured, does not change so often. But the data on their weight can vary every day. Static-dynamic directories are the only directories where you can consciously repeat any information. Another example would be the salary reference for posts (by job code).

Job ID	Salary	Update Date
1001	12,000	02/05/2015
1002	17,000	02/01/2015
1003	11,500	02/01/2015
1004	25 450	02/01/2015
1005	10,000	02/01/2015
1006	6,000	02.02.2015

Table 2. An example of static-dynamic directories

Dynamic reference books are tables, data about objects, subjects, links in which change frequently and are used in other tables. They differ from static reference books only in the frequency of data modification in them.

An example of such tables may be lists of projects. In fact, data on the opening or closing of projects may be in the directory of projects, which in most cases is wrong and violates the integrity. On the other hand, if you keep a history of changes in the opening and closing (suspension) of projects, you can get data redundancy. The integrity and redundancy of the data will fight each other for a long time, as well as winter with summer.

Project code	Project	Standard deadline	Date added	User
PT102	Painting windows	15	01/03/2014	1547
PT103	Door installation	ten	01/04/2014	9874
PT587	Fire Crane Inspection	2	01/04/2014	1456
PT588	Replacing hatches	3	01.02.2014	0147
PT133	Channel cleaning	eleven	02/09/2015	1547

Table 3. An example of dynamic directories


Figure 2. Reference types

1.3. Types of ligaments

Table-bundles can be divided into two types.

This is a reference book (we’ll immediately clarify that a reference book is not a reference book, so named because there are fields in it that make up the reference book but cannot be selected in the reference book). The table in which foreign keys are stored, data that are not reference, and fields containing data that form the reference book but cannot be separated into a separate reference table.

An example of a reference-bunch will be a table of payment transactions. Or a table with data about a football match.

Transaction code	Payer	Recipient	Amount	date	Comment
EEVS-doodi4	100045	57457	-10 000	07.25.2014	On boots
UDFD-ioeed9	455780	10024	-900	06.24.2014	Null
Pedd-jdksl4	144770	56698	-6980	01/01/2015	Null
FDFE-keiiii0	447757	one	120	07/08/2014	Null

Table 4. Sample reference-bundles

And a bunch (yes, just a bunch). This is a table in which only foreign keys and data that cannot be attributed to reference data are stored, for example, the date or values ​​of logical fields.

An example of a link will be a table for automatic logging of a data processing terminal.
By the way, it is easy to guess that the bundles are almost never used, because most often there is data that can be written to the database, but not contained in reference books, so it is impossible to compare them with a foreign key.

Code	Client code	Meter readings	Month
2334	35643	50	01/01/2015
2335	235673	49	01/01/2015
2335	436345	56	01/01/2015
2335	574733	24	01/01/2015

Table 5. Sample bundles

It is necessary to clarify what these fields are that form the reference book, but cannot be separated into a separate reference table. Examples of such fields are the “comment”, “complaint”, “description”, “sentence” fields. In short, if you give a popular example, the field "message" in the database table of any social network ...


Figure 3. Types of bundles

2. Generalization of classification

2.1. Table Classification

Table view	Description	Examples	Pros (+)	Minuses(-)
Static reference	Table. Data from it is taken for other tables. From the directory in other tables, you can only use the primary key. A static directory should contain information that either does not change at all, or changes so rarely that it can be neglected. A static directory is referenced (foreign key) when it is necessary to obtain names, designations, norms, quantitative or qualitative indicators. Other	Directory (names and numbers) months. Directory of warehouses and workshops of the enterprise. Handbook of rules of the game.	Sometimes it replaces the system functions of the DBMS, allows for more flexibility in working with some data. In the event that rarely changing information changes, it warns against serious consequences.	Using a table with any structure can slow down work, in case the table replaces the system storage. We have to write additional functions and processing for this table, which are not always correctly optimized. In some cases it is impossible to optimize.
Static dynamic directory	Table. Data from it is taken for other tables. From the directory in other tables, you cannot use the external key of this directory, but you can use the primary key.	Salary reference for posts. Reference (shoe sizes, weight, height, head size) physiological parameters. Directory (of managers, companies) containing companies and managers who these companies serve and take into account.	Allows flexible normalization according to the Schedule-bundle = Bundle + Static Dynamic Reference Guide.	The reference book, separated from the reference book, does not disappear anywhere and does not have any relational relationship, which would allow it to turn into a static or dynamic reference book. So, it is always redundant.
Dynamic reference	Table. Data from it is taken often for other tables. From the directory in other tables, you can only use the primary key. A dynamic directory should contain information that changes frequently.	Directory of customers. Directory of suppliers. Directory of counterparties. Directory of company managers. Handbook of workers. Handbook of students.	Allows you to store dynamic data, while giving the opportunity to uniquely refer to them.	Most of the cumulative type and do not divide, which creates a certain redundancy.
Reference-bundle	Table. Data from it cannot be contained in other tables, but on the basis of them data in other tables can be created.	Payment transactions Sales Interplant displacement. Transportation schedule	Allows flexible normalization according to the Schedule-bundle = Bundle + Static Dynamic Reference Guide.	A reference book after normalization turns into a bundle and minimizes data redundancy without affecting the integrity, but is not divisible and is not optimized when archived in the current table.
Bunch	Table. Data from it cannot be contained in other tables, but on the basis of them data in other tables can be created. The table cannot contain tuples, the attribute values ​​of which are indivisible and not unique.	Automatic error log in the program. Server request log Trace results. Reports on unloading and loading components. Automatic security system reports.	A bundle minimizes data redundancy without affecting integrity.	Accumulating, is an indivisible table. Difficult to optimize.

Table 6. Classification

2.2. Classification in schematic form

Figure 4. Table classification scheme in relational databases based on integrity and data redundancy

3. Some comments on the use of classification

3.1. Apply classification when normalizing tables

The process of normalization, if some stages are not taken into account (But consider the results of these stages!), Is the usual “splitting” of tables into smaller tables with the creation of a relational connection between them directly or through intermediate tables (a “many to many” connection). Relational relation can not always be understood as a relational relation!

Converting a dynamic or static reference to a static-dynamic reference, and a reference-bundle into a bundle, just like a static-dynamic reference guide into a reference bundle, is nothing more than splitting tables. That is, converting one type of table to another through the above classification in order to avoid data redundancy - this is how normalization can be defined (one of the definitions).

For example. Suppose there is a database in which the only data modification operation is an add. In this case, it becomes ineffective every time when a particular attribute of an entity is changed, to “copy” the other attribute values ​​into a different tuple. In this case, NULL or the creation of a static-dynamic directory is used, where a number of attributes of one semantics or one attribute is described, and only the foreign key with the primary key of the sequence is duplicated. The same method can be used in the traditional data modification scheme with updating and deleting data.

Conclusion

This classification was created by me on the basis of observations in the design of databases, as well as on the basis of the read theory on the design in relational DBMS. To my friends and acquaintances who study the “database” discipline and engaged in database design, and to me this classification has rather seriously simplified “life” and allowed in many situations to choose the most suitable and, as it turned out later, the correct type of table for storing in it or other data.

The classification can be extended by splitting the existing species in it into subspecies (perhaps even adding new species). This classification also showed that in some situations it is better not to use one or another kind of tables. Some types of tables from this classification are best used less frequently (dynamic references). And some try to replace with others (reference-bundles for bundles).

I hope that no one else will help this classification in mastering the discipline "Databases" and in designing databases in relational DBMS.