Main concepts about database tables

Previous Top Next

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.