Database design

Database design is the planning and creating a database. Since there are a lot of different types of databases, such planning also typically falls differently.

Design of relational databases

The structure of a relational database is usually modeled by an ER diagram. If performance-critical hits are imminent, then you can follow one step denormalization. Many data modeling tools offer a support that from the data model the SQL DDL statements can be generated to build the database. Executing the SQL DDL statements, the database objects are created:

  • Scheme
  • Tablespace
  • Table
  • Constraint
  • Index
  • View

After the database objects are created, the data sets can be inserted into the database.

Plan space

For databases with a large volume of data, it is important to plan the needed space exactly.

Scope

The memory footprint of the entire database is essentially determined by the data volume of each table. If one estimates the average sentence length of a table and the expected number of records, then these two values ​​must be multiplied together to estimate the storage space required for the entire table. The memory requirements of each table is added and the space required for all obtained in the database to be stored. Often such figures for the entry into service with a planning of the annual growth rate can be calculated.

To the square of the net data are usually 10% to 50% more storage space requirements for structures for managing the database (eg indices, Internal tables for managing access rights and disk space management ).

Quality of the storage medium

Selecting the appropriate hardware plays a significant role in large databases. It offered hard drives with different sizes and access speeds. Since all hard drives carry a low indeed, but existing risk of data loss, divergent approaches to reduce these risks have been developed. (See Redundant Array of Independent Disks (RAID) and Storage Area Network ( SAN) )

Backup

For each database, the regular production of data backups should be scheduled. The key parameters are the space requirements of the backup and the time that is needed in case of total failure of the storage medium to resume operation. Also, the time required to restore individual tables, is an important variable. Furthermore, it must be determined how long backups are to be canceled.

Reliability

If a failure of the database for a few days is not critical, then it can be replaced and the operation can be continued after a loading a backup in case of failure of a system component.

However, if critical data are stored in the database, then it may be that a failure of a few hours already caused immense damage, as important business of a company can no longer be executed. High availability of a system is the ability to ensure full operation in the event of its components. This is achieved firstly by a redundant feature in all system components. On the other hand a supply of well-trained staff is needed that can immediately take the necessary measures in case of error.

Costs

The cost for the storage of data depends on a number of components.

Often the cost of licenses and support for the database management system (DBMS ) are considerably higher than the cost of the hardware. Even if you opt for free DBMS software, a support contract is in mission-critical applications still advisable to get help quickly when problems arise.

With the cost of the operating system, it behaves the same as with the cost of the database license. For mission-critical applications, a support contract will be recommended to quickly get problems with the operating system support. For large databases fast and often there are further costs for software for fail-safe management of hardware components (such as Veritas Cluster Server ) and to manage the backups to.

The cost of computer hardware are often significantly lower than the licensing and support costs. However, many database vendors licenses on the hardware used and, for example, the number of processors are dependent. In order to make the system fail-safe as possible, all hardware components are often configured redundantly.

For large applications you will need skilled personnel for the operation of the database, which provides a service around the clock, for example, and can quickly take the necessary measures in case of problems.

Software

Database model Data Definition Language Table (database) Database index Row (database) Structured systems analysis and design method
219664
de