Database design
Database design is a structured approach to creating a framework in which data can be stored persistently and accessed efficiently. It involves a series of steps that begin with understanding the information needs of an organization and culminates in the development of a functional database. The process requires a blend of technical and intellectual skills, sparking debate about whether it is more of an art or a science.
Historically, electronic databases emerged in the late 1950s for military use, expanding into business and academia by the 1960s. As technology has evolved, so too have database design methods, adapting to the growing complexity of organizational information requirements and user sophistication. A typical database design process includes several phases: requirements analysis, conceptual design, hardware and software selection, logical design, physical design, and iterative testing before deployment.
The methodology emphasizes building a detailed understanding of both data relationships and the business processes that generate data. By adhering to established steps, database designers can create systems that meet specific organizational needs while ensuring data integrity and security.
On this Page
Subject Terms
Database design
Database design comprises the plan, models, specifications, and instructions for creating a structure (database) where data can be stored in a permanent (persistent) manner. Users can extract that data in combinations that answer questions in a particular area (domain). Successful database design requires a complete and current understanding of database technologies and methods. Further, designers must understand how people work and the types of questions a database will answer. Because of the wide variety of technical and intellectual skills that come into play, there is an ongoing debate as to whether database design is an art or a science.
Overview
Databases store data so users can access it to provide information and insights into how an organization is functioning. Electronic databases, as we currently use the term, date back only to the late 1950s when they were developed for the US Department of Defense. By the 1960s, databases were being designed and created for business and academic use.
In the intervening years, database design has changed in response to increased computing capabilities and, just as importantly, to the changing needs of organizations and the increasingly sophisticated information expertise of potential users.
Several database design methods have come into favor over the years, resulting from an evolution in how users and designers have looked at data and how it can serve their needs. Organizations’ information requirements have become larger and more complex; how people look at information and its use has also grown more sophisticated.
Variations in database design exist, but methodologies generally follow a sequence of steps starting with gathering information and user requirements through choosing the appropriate software and hardware technologies and then iterative development and testing. A six-step process defined by the University of Liverpool’s Computer Science Department is typical.
The first step in the Liverpool methodology is the requirements analysis where an organization’s information needs are documented. What does the organization need to know? What pieces of data are required to form the picture that the organization requires? How are these data to be organized and relationships defined? The second step, conceptual database design, identifies all of the pieces of data and puts them into a model in which their place and relationships are also defined. At the same time, the business processes that create the data are also modeled. This second step is extremely critical because it requires that the database designers thoroughly understand the customers’ needs and way of working. Often a problem arises at this stage because while database designers are experts at databases, they do not always have similar expertise in the organization’s business area.
The third step is the choice of hardware and software, including the choice of security programs to protect the database. In some instances, the available options are wide, allowing potential users to get the capabilities they need on an affordable scale.
The fourth step is logical design. Business processes (transactions) are documented and diagrammed, followed by a mapping of these workflows to the capabilities of the selected (or newly developed) database. The fifth step (physical design) determines where data will be stored and how it is moved into the database.
The final step is planning how the database is actually developed with continual (iterative) testing at every stage until installation and use when it is then usually managed by a company’s database administrator.
Microsoft also offers steps for database design, similar to those mentioned above. In the preliminary processes, determining the purpose of the database and collecting the information needed to compile the database are foundational to the design process. Next, information must be organized into tables and divided into appropriate columns. The table or tables must then be assigned primary keys, and relationships between keys must be established. After the core elements of the database are established, designs should be scanned for errors, tested, and normalized using data normalization rules.
Bibliography
Badia, A., and D. Lemire. “A Call to Arms: Revisiting Database Design.” Sigmod Record, vol. 40, no. 3, 2011, pp. 61–69, doi.org/10.1145/2070736.2070750. Accessed 20 Oct. 2024.
Buxton, Stephen. Database Design: Know it All. Morgan Kaufmann, 2009.
Churcher, Clare. Beginning Database Design: From Novice to Professional. 2nd ed., Apress, 2012.
Creswell, John. W. Research Design: Qualitative, Quantitative, and Mixed Methods Approaches. 5th ed., Sage, 2018.
Currim, Sabah, et al. “Using a Knowledge Learning Framework to Predict Errors in Database Design.” Information Systems, vol. 40, pp. 11–31, doi.org/10.1016/j.is.2013.08.001. Accessed 20 Oct. 2024.
"Database Design Basics." Microsoft, support.microsoft.com/en-us/office/database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5. Accessed 20 Oct. 2024.
Grad, Burton, and Thomas J. Bergin. “History of Database Management Systems.” IEEE Annals of the History of Computing, vol. 31, no. 4, 2009, pp. 3–5.
Hernandez, Michael J. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design. 4th ed., Addison-Wesley, 2021.
Stephens, Rod. Beginning Database Design Solutions: Understanding and Implementing Database Design Concepts for the Cloud and Beyond. 2nd ed., John Wiley & Sons, Incorporated, 2023.