Computer programs work with a tremendous amount of information that needs to be stored somewhere. Especially for this purpose, databases are created that provide a structured presentation of information and convenient access to it. One of the most popular ways of organizing such repositories is tabular, in which special types of fields can be selected for different types of information. This makes data manipulation easier and saves resources.
Table Databases
Tabular, or relational, databases are widespread because of their convenience and well-developed infrastructure. There are many database management systems that provide complete control over application information.
Each base consists of several tables representing a specific entity or relationship of entities. For example, in the form of a table, data on university students or information on the results of exams can be presented.
Columns in a table are called fields and contain a specific attribute of an entity. So, in the table "Students" as fields are:
- Full Name;
- record number;
- Date of Birth;
- phone number.
Strings are called records and represent a separate real object (of a particular student).
The number of columns (fields) of the table is determined during its creation and is no longer changed. Lines can be added, deleted and edited at any time.
At first glance it is obvious that the information stored in the field "full name" Significantly different from the information in the "Grade No." field or in the "Date of birth" field. Since manipulations with different types of data are carried out according to different algorithms, it is advisable to determine in advance what kind of information will be stored in a specific field of the table.
Each database determines which types of fields it can process. The main types of information, for example, numerical, symbolic, are supported in any system. In addition, some databases may provide their own data types.
Fields and their properties
A record field is the smallest named unit of information in a database. It has two required properties:
- a unique name within the table by which it can be accessed;
- The type of data stored in it.
The field can be marked as unique or key.
The uniqueness property means that for all table entries, the value of this field cannot be repeated.
Key fields are those that are most actively involved in data selections. Indices will be built on them - additional structures to facilitate the search.
Each table in the database must have a primary key that is unique to each record and uniquely identifies it. It may consist of one or more fields. It is most reasonable to choose fields with short values as the primary key. For example, in the “Students” table, the field “Set-up No.” can be used as the primary key.
Integrity properties
For the database to function correctly, it is crucial that data integrity is maintained. This means that each field of each record must take exactly the value that is expected. For example, the set-off number will always be a number, and there should not be numbers in the student’s name.
In addition, some fields are absolutely necessary to describe the entity, while others are optional. The student may not have a phone, but there is always a name and a record.
Ensuring data integrity is controlled by several properties:
- the type of field determines the type of data that may be its value;
- obligatory prohibits entering records into the table with an empty field;
- the default value allows you to leave the field blank but not leave it blank;
- uniqueness provides unambiguous identification of the entity within the table;
- maximum or exact length of the field value in characters;
- method of formatting data;
- various additional conditions (maximum and minimum date).
The main types and formats of fields supported by most DBMSs:
- numeric - integer and real;
- string
- binary
- brain teaser;
- date and time;
- enumerations and sets.
In some databases, hyperlinks, monetary values, notes, error messages can be allocated as a separate type.
A field type defines a set of integrity constraints that can be applied to it. In many database management systems, these types are specified and initially include a number of limitations. A good example is the TINYINT type in MySQL, which accepts integers in a limited range.
Lines
String values can contain any characters. The main restriction is imposed on the length.
Strings can be fixed or variable length. In the second case, the maximum possible size is usually set. The most common length limit for rows in a database is 255 characters.
The names of string field types in different DBMSs may differ. Most popular:
- CHAR - fixed length up to 255 characters. If the line size is less than the set, it will be supplemented with spaces.
- VARCHAR, TINYTEXT - variable length up to 255 characters, an extra byte is spent to store the size.
- TEXT, MEMO - variable length up to 65.535 characters.
- MEDIUMTEXT - A maximum of 16.777.215 characters.
- LONGTEXT - A maximum of 4,294,967,295 characters per line.
The string type of the database field allows you to store passwords, short descriptions, personal data, addresses, phone numbers, articles in it. No mathematical operations are performed with this information. Strings can be compared in lexicographical order.
Very large chunks of text can also be stored in BLOB fields, which are discussed below.
Possible integrity constraints: string length, mandatory, default value.
The numbers
There are a large number of numerical formats supported by databases: integer, long integer, real, fractional, floating-point and fixed-point.
Math operations can be performed on numbers. The database can store both positive and negative numeric values. DBMSs define several numerical types that have different size limits.
For integers:
- TINYINT, byte - the range of values is 0 - 255 (or -127 - 128);
- SMALLINT - from 0 to 65.535 (from -32.768 to 32.767);
- MEDIUMINT - from 0 to 16.777.215 (from -8.388.608 to 8.388.607);
- INT - from 0 to 4294967295 (from -2.147.483.648 to 2.147.483.647);
- BIGINT - from 0 to 18.446.744.073.709.551.615 (from -9.223.372.036.854.775.808 to 9.223.372.036.854.775.807).
For real numbers:
- FLOAT - the number of characters in the fractional part of the mantissa is not more than 24.
- DOUBLE, REAL - double precision number, after the point there can be up to 53 characters.
There is another specific type of database field - DECIMAL (NUMERIC). This is the same number as DOUBLE, however written as a string.
Always select the smallest possible field size. For example, a single TINYINT byte is enough to hold a student’s exam score. This saves database resources. For example, BIGINT fields are among the most rarely used types, since practically no application operates with numbers in such a huge range.
Possible data integrity limitations:
- the size;
- data formatting (in some DBMSs): numbers can be presented in percentage, exponential, monetary format;
- fractional part size;
- default value
- uniqueness;
- autofill (numbering of entries).
Fields with a numeric data type often become the primary index of the table (provided that the values are unique).
Counter
Counter fields have a numerical data type, but their value is assigned to each new record automatically, by the database itself. Each time, the counter simply increases by one, ensuring the numbering of rows in the table.
Due to their uniqueness, such fields can be used as a surrogate primary key, because they allow you to uniquely identify each record.
Counters do not have integrity constraints, since the database itself takes care of their completion.
date and time
Fields with the data type "Date" and "Time" are very convenient for working. They allow you to save data in various formats:
- DATE - only date in the format "YYYY-MM-DD", for example, "2018-04-04";
- DATETIME - date along with time in the format "YYYY-MM-DD HH: MM: SS", for example, "2018-04-04 17:51:33";
- TIME - only time in the format "HH-MM-SS";
- YEAR - year in the format "YY" (17) or "YYYY" (2017);
- TIMESTAMP - timestamp, which can indicate, for example, the exact moment of entry in the database. The format may be different, for example, "YYYYMMDDHHMMSS".
The main integrity constraint is the way data is formatted.
Boolean values
The simplest type of information is logical, or boolean. It allows only two mutually exclusive values: TRUE (true, 1) and FALSE (false, 0).
Fields with a logical data type are used to store so-called flags, which can be used to mark whether a student receives a scholarship or not.
Binary data
Databases provide the ability to store large amounts of information. Audio and video files, images, fragments of compiled code are stored in BLOB form (Binary Large Object, binary large object).
Fields intended for recording such data must have one of the following types:
- BINARY - a binary string of fixed length;
- TINYBLOB;
- BLOB
- MEDIUMBLOB;
- LONGBLOB;
- OLE object (Object Linking and Embedding, technology for linking and inserting objects) - in Microsoft Access;
An array of binary data has no user integrity constraints. Different databases implement differently with blob objects.
Transfers
In some DBMSs, it is possible to create a field whose value will be selected from a predefined list of valid values. This is very similar to the way radio buttons work in HTML.
This type of field is called ENUM. The allowed list can have a maximum of 65.535 string values, of which only one is selected.
The integrity constraint in this case is obvious - all possible values of the base field are predefined and cannot take other values.
Many
The data type SET works very similarly. It also accepts a list of valid string values, but allows you to select several of them at once. This is how the checkbox element works. The maximum number of elements in a set is 64.
Choosing the right type of database field is of great importance for the organization of the application. This is due to resource saving and various methods of processing information of various kinds.
When designing and creating a database, it is important to precisely determine the format and integrity constraints of information in each field of each table. Of the suitable types offered by a particular DBMS, it is recommended to choose the one that takes up the least space.