Database Design Process

Relational Database Design Process

            A relational database’s design is based on the relationships that different entities in the database have with each other. This approach allows for data to be navigated in logical and user-friendly ways. In the early stages of database design all of the different features of the database will be present but poorly organized. Good database design allows ensures that these entities will be displayed logically, and data can be easily interpreted and manipulated.

Planning

            Using a database planning tool before any features of the database are hard coded into the design will prevent an access of work. A metadata worksheet was used to design the midterm database. A metadata worksheet, as its name suggests, shows data about the data. A spread sheet is made that normalizes the data into its atomic values and describes what features that column will have. Features include any unique, check or foreign key constraints, nullability and primary key status. An ERD may also be used in addition to the metadata worksheet and will show if tables have one-to-one, one-to-many or many-to-one relationships and highlight any foreign keys.

            Views, stored procedures and test code are typically created for each table in the database. Planning the order that these features will be made ensures that none are forgotten.

Execution and Implementation

            The planning tools and order created in the planning process allows the developer to follow a guided process. Initial tables are made using the normalized content from the metadata worksheet; constraints are subsequently added. Appropriate views are made from these tables, a view should be made for each table as well as any overview views or joined views to meet a specific need. Stored procedures to insert, update and delete the contents of each table should be will be made with the proper test blocks and descriptive error messages to increase usability. Execution of each procedure guarantees its functionality and serves as a “proof-read” of the code. From there, privacy constraints are set to ensure that the database keeps its integrity.

Conclusion

            Having a stepwise process when designing a database ensures a that nothing will be forgotten and the design is logical. The process is streamlined and easy to share with others making it easier to divide the labor of the coding process.