Main concepts about database tables

For one table several files are created which contain data, indexes, keys etc. Name of the main file of the table (file containing the data) - it is a table name - is set at creation of the table. The names of remaining files of the table are assigned automatically, and the names of all these files coincide with the table name, except of extensions of names; the different extensions indicate onto contents of the appropriate file.

Each DB table consisting of lines and columns is intended for a storage of information about one-type objects of the system. The table line is named as record, and table column - field.

Keys and indexes can be defined in the tables. Key is a combination of fields, the data in which uniquely define each record in the table. The simple key consists of one field, and complex (composite) key - from several fields. Fields, on which the key is built, are named key fields. The key serves for unique identification of the table records, and also for preventing repetitions of the key values. Thus the values of separate fields of a composite key can repeat. Such key is named as a primary key.

Index, as well as a key, is created by the table fields, however it can admit the values repetition of components of its fields. Fields, on which the index is built, are named index fields. The indexes are named at their creation. The index serves for tables sorting according index fields. Besides at usage of indexing, the search rate of data in the table become s higher.

The following table lists the types of fields of dBASE tables, and also the description of values that can contain a field of viewed type.

Type	Values description
Character	Characters line. Length no more than 255 characters
Date	Date
Logical	Logical value. The following values are allowed: T and Y (True), F and N (False).
Memo	Line of characters. Length is not limited. The characters are stored in the file with DBT extension
OLE	Data are in the format, which is supported by OLE technology. The data are stored in the file with MB extension
Number	Floating-point number. Range: -1038-1038-1. The accuracy of 15 digits of mantissa.
Binary	Sequence of bytes. Length is not limited. The bytes contain arbitrary binary value.

The following table lists the fields types of MS SQL tables.

Type	Values description
bigint	Integers of the big length. Range from-9 223 372 036 854 775 808 to 9 223 372 036 854 775 807
bit	Integer data with values only 1 or 0. Usually are replaced with constants 'T' and 'F'.
datetime	Date and time from January, 1st, 1753 till December, 31st 9999 year
float	The numerical data with fixed precision. Range from -1038-1 to 1038-1.
image	The binary data of variable length. The maximum length of 231-1 (2147483647) bytes.
int	The numerical value (integer) ranging from -2147483648 to 2 147 483 647
smallint	Integer data
tinyint	Integer data in a range from 0 to 255
money	Monetary data. Range from -263 (-922337203685477.5808) to 263 -1 (+922337203685477.5807), accuracy to 1/10000 of monetary unit.
numeric(24,6)
text	Character data of variable length. The maximum length 231-1 (2147483647) characters.
uniqueidentifier	Unique number stored in a 16-byte binary string
varbinary	Binary data of variable length. The maximum length of 8000 bytes.
varchar	Character data of variable length.
varchar(MAX)	Character data of variable length. The maximum length of 8000 characters.

The table contains a list of fields types of MySQL tables.

Type	Values description
varchar	String of not more than 255 characters
char	String of the fixed length which on the right is supplemented with spaces till the specified length, at storage. A range of length from 1 to 255 characters.
bigint	Integers from -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807
datetime	Date and time in the format YYYY-MM-DD HH:MM:SS
int	Integers from-2 147 483 648 to 2 147 483 647
tinyint	Integers from-128 to 127
decimal(24,6)	Numerical data with fixed precision. Range from -1038-1 to 1038-1.
double	The number of floating-point double-precision
float	A small (single-precision) floating-point number. Ranges from –3.402823466E+38 to ––1.175494351E-38, 0 and 1.175494351E-38 to3.402823466E+38.
smallint	Integers from -32 768 to 32 767
text	String of not more than 65 535 symbols
year	Year in the 2- or 4-x digitally
timestamp	Date and time in the timestamp format
longblob	Binary data no more than 4 294 967 295 characters
blob	String of not more than 65 535 symbols
enum	Enumeration. A string-object which can accept only one value, is chosen from list of values «value 1», «value 2» or NULL. ENUM can have maximally 65535 various values.
set	Set. A string-object that can have zero or more values, each of which must be chosen from the list of values «value 1», «value 2», ... SET field can have a maximum of 64 variants of values.