An Intro to Database Design
Every website, software application, or mobile app requires a functioning database to operate. Whether it contains information about POS data for a retailer or contact information for political campaign contributors and donors, such information and data must be organized and structured to be useful.
Database design follows the Database Development Life Cycle (DBLC) and is modeled on the Software Development Life Cycle (SDLC). The DBLC can contain several iterative steps depending on the situation. Though there are many approaches to designing a database, the overall approach is usually consistent throughout. The most important steps are delineated below to help you better understand the process, and make better decision regarding database design down the road.
The Initial Study and Assessment
This is the first phase of the DBLC, similar in many ways to the planning phase of SDLC. An effective initial study phase helps determine the purpose of the database to help plan and prepare for subsequent steps as the project progresses. Specifications are detailed in the form of written documents containing client requirements and mockups which are then analyzed for accuracy and necessity. This phase consists of deep analysis of the data environment and ecosystem.
A thorough analysis of the client, its requirements, organizational structure, operating environment, and the nature of applications you are designing for is critical for success. Without a complete understanding of the various factors and variables at play, there can be a significant impact on the specifications and designing in later stages.
For instance, the database might have to be built for a small business or it could be for a complex corporate client. Both instances have profound differences and nuances to attend to. By having a well-developed mission statement it can serve as a guideline throughout the designing stage.
Prior to execution determine whether the company is an expanding one, whether it is dynamic in its requirements, or whether the nature of the application is transactional or analytical. Identify constraints such as where the company currently stands as regards to the current systems, methods and processes. Focus on issues surrounding the current situation, existing training opportunities, and any other limitations.
Define objectives and gather information such as what objectives the database is going to fulfill, what it is looking forward to store, what other kind of interactions is expected and any other scopes and boundaries that needs to be defined and analyzed.
What Methods Are Involved with Database Design?
The first step, creating a conceptual database design allows for easy communication between developers and end-users and is a permanent description of database requirements. Following are the salient features of this phase:
- It starts with building a model of the data that will be used based on the requirements of the system and is independent of all physical considerations such as what Database Management Systems (DBMS) would be used.
- There are many techniques to create a conceptual model in the form of the Entity-Relationship (ER) model, Relational Model (RM), the table model etc. out of which the ER model is the most popular conceptual model for database design.
- In the ER model, all the data that you would want to include in your database is listed as ‘entities’, their features labeled as ‘attributes’ and all the entities inter-related through ‘relationships’.
- Once the entities and relationships are identified, next, attribute domains are established and primary and alternate key attributes are determined.
- After this these written requirements are converted into E-R diagrams.
- Thereon this conceptual model is validated and reviewed against user transactions.
Next step is the creation of a logical database design in which the conceptual model constructed in the previous step is now converted into an internal logical model. Following are the salient features of this phase:
- Once the conceptual model is validated and reviewed, the data is then arranged into a logical structure (tables, indexes, and views) and mapped to specific storage objects supported by the targeted DBMS. (Image from: http://docs.oracle.com/)
- If the DBMS is a relational database, data is mapped to tables (which store data in rows and columns) and if the DBMS is an object-oriented database then data is mapped to objects (used by Object Oriented Programming language).
- Once all the relations have been mapped, data is then validated using normalization techniques (process of organizing data to minimize redundancy).
- This logical model is then reviewed and validated with the help of user transactions.
The third and final step is creating a physical database design wherein the description for the implementation of the logical data model on secondary storage is created. Following are the salient features of this phase:
- Now all the data is translated and loaded onto tables.
- The base relations and the general constraints are designed using the SQL language.
- All the transactions are analyzed.
- The file organizations and indexes are chosen and the required disk space is configured.
Database Implementation and Loading
Once the conceptual, logical and physical designs have been configured we come to the next step of implementation and loading wherein the physical realization of database design is done. Following are the salient features of this phase:
- If there is a need for a new DBMS (like SQL Server or Access) then the DBMS software is selected after a careful consideration of the advantages and disadvantages of different DBMS packages available, their tools and features, and the hardware requirements.
- The DBMS is installed on the machine and the database itself created in the DBMS.
- Data is loaded onto the system to start of the DBMS.
- The efforts done so far are finally implemented and your database is now up and running!
At this stage, the database is tested and fine-tuned against the specifications provided by the clients. Developers continuously try to make sure that the database is performing as planned. The systems are also evaluated in-situ to check for relations with other linked applications.
Maintenance and Evaluation
At this stage, the developers and designers try to fix problems associated with the systems which are sure to crop up time and again (no matter how perfectly designed!). They also suggest enhancements and identify new requirements.
Lynda Tutorial on Database Design
Additional Database Design Resources
- How Amazon Is Building Substations, Laying Fiber and Generally Doing Everything To Keep Cloud Costs
- In Relational Database Design, Don't Shortchange Requirements Stage
- Big Data Results Bigger and Better Insights For Decision Makers
- The Right Database Design Can Maximize Key Insights For Your Enterprise