Building houses require a blueprint. The blueprint identifies the placement of doors, windows, and outlets. It defines the size of the rooms, whether there is a basement or crawlspace, and the location of the furnace. This plan outlines the skeletal structure and outer skin of the house which directly translates into the integrity and ease of use of the structure. In database terms, it schema design is the blueprint of the house in which your data will live.
Much like the construction of a house, a database consists of a combination of database objects. A few examples of database objects are tables, views, stored procedures, and indexes. In a relational database schema, there are relationships defined between objects. For example, a table consisting of customers may have a relationship established with a table comprising customer transactions. The basis on which the depth of these relationships patterns is a technique called normalization.
The arrangement and definition of these objects will have a direct influence on the stability, usability, and integrity of the database. For example, a well-normalized schema works well for a database that requires efficient storage and high input, known as a transactional model. However, a lesser-normalized schema works best for a database that needs efficient read and aggregation, known as an analytical model.
Another consideration in the design of a database schema is the duration of any given transaction that occurs in the database. Long-running transactions consume the limited resources of the server in which the database resides, such as memory (RAM), processor (CPU), and disk (I/O). The more resources that are consumed by a transaction, the less is available for other operations which result in a perceived (and actual) slowness. Another aspect that comes in to play is an operation called a “lock.” Locks manage access to rows and tables. Poor design of the database and long-running transactions can increase the duration and level of these locks, which generate frustrated groans from the users, and may result in data loss.
These are only a couple of examples of why a quality schema design is essential to design your database with intention. A database is much more than a barn which provides storage of a miscellany of data until needed. It is a structure worthy of the application of careful design. Do you have a schema design that was the victim of a hasty schema design? There is hope. The review of a database schema can occur without disruption of its operation. Much like a house, the addition and upgrade of objects are possible. The remodel of existing structures to better fit your needs can be implemented. However, even these improvements require a blueprint, a plan, a design. As you dive into such engagements, be sure that your efforts include a well-defined schema design.