Database design

from Wikipedia, the free encyclopedia
Database diagram

Database design is the planning and creation of a database . Since there are a large number of different database types, such planning also typically turns out differently.

Design of relational databases

The structure of a relational database is mostly modeled by an ER diagram . If performance-critical accesses are to be foreseen, then another step of denormalization can follow. Many data modeling tools offer support so that the SQL- DDL statements for building the database can be generated from the data model . The database objects are created by executing the SQL DDL statements:

After the database objects have been created, the data records can be inserted into the database.

Plan storage space

For databases with a large volume of data, it is important to plan the required storage space precisely.

scope

The storage space requirement of the entire database is largely determined by the data volume of the individual tables. If you estimate the average record length of a table and the expected number of data records, then these two values ​​must be multiplied to estimate the storage space requirements of the entire table. The storage space requirements of the individual tables are added up and you get the space requirements for all data to be stored in the database. Such figures are often calculated for the time of commissioning with a planning of the annual growth rate.

In addition to the space for the net data, there is usually a further 10% to 50% additional storage space requirements for the structures for managing the database (e.g. indices , internal tables for managing access rights and storage space management).

Quality of the storage medium

The selection of suitable hardware plays an important role in large databases. There are hard drives available with different sizes and access speeds. Since all hard disks carry a small but nonetheless present risk of data loss, different concepts have been developed to reduce these risks. (See Redundant Array of Independent Disks (RAID) and Storage Area Network (SAN) )

data backup

Regular backups should be planned for every database . The essential parameters are the space required by the data backups and the time that is needed to resume operation in the event of a total failure of the storage medium. The time it takes to restore individual tables is also an important factor. In addition, it must be specified how long data backups should be kept.

Resilience

If a failure of the database is not critical for a few days, then if a system component fails, it can be replaced and operation can be continued after a data backup has been imported.

However, if business-critical data is stored in the database, then an outage of just a few hours can cause immense damage because important business of a company can no longer be carried out. The high availability of a system describes the ability to guarantee unrestricted operation if one of its components fails. On the one hand, it is achieved through redundant equipment of all system components. On the other hand, well-trained staff must be made available who can take the necessary measures immediately in the event of a fault.

costs

The cost of storing the data depends on several components.

Often the costs for licenses and support for the database management system (DBMS) are significantly higher than the costs for the hardware. Even if you opt for free DBMS software , a support contract is still advisable for business-critical applications so that you can get help quickly if problems arise.

The cost of the operating system is the same as the cost of the database license. A support contract is recommended for business-critical applications in order to receive support quickly in the event of problems with the operating system. With large and fast databases, there are often additional costs for software for fail-safe management of the hardware components (e.g. Veritas Cluster Server ) and for managing the data backups.

The costs for the computer hardware are often significantly lower than the license and support costs. However, with many database manufacturers the licenses of the hardware used and z. B. the number of processors dependent. To make the system as fail-safe as possible , all hardware components are often set up redundantly.

In the case of large applications, you will also need skilled personnel to operate the database. B. is on call around the clock and can quickly take the necessary measures in the event of problems.

software

See also