Fundamental concepts of database design

To achieve coherence, consistency, broad general outlook, capture sufficient information, iterative development, and independent convenient update by separating design layers it is extremely important that the fundamental concepts of database design are followed. The first step is the concept of gathering information from the business rules and its people. The designer should understand the purpose of the database, the audience, and its intended function. Once the information has been gathered the designer should extract the main themes or subjects or classes which could be used as entities. From these entities and their business nature, relevant attributes could be divided across the entities. All these could be captured through the ER model. In this stage, relevant data views for relevant stakeholders could be identified. ER model will allow some kind of logical independence separate from actual implementation in the DBMS. From the ER model further precision and details could be achieved through the relational model or the object model if, for example, a programming language will be accessing the database. There are tools that generate code from the object model for CRUD operations. In the relational stage, normalization should be achieved and relevant rows, columns, table names, etc should be realized. Keys, constraints, and relationships among tables should be implemented as well. Once these considerations are completed a suitable DBMS should be chosen such as MySQL or Oracle SQL to physically implement the database. In this stage data types, functions, indexing, etc are implemented as well as security considerations, concurrency, and data backup protocols are realized.

There are some data models that have been used historically but the most used data model today is ER model and the relational model. I think both of these two models complement each other and need each other for transitioning from abstraction to details. ER model captures the highest layered abstraction while the relational model further breaks it down into details of tables, rows, columns, keys, constraints, and normalized form. If a programming language is going to access the database then the object model will be suitable to generate programming-specific SQL statements.

(Database Design Basics, n.d.; DBMS — Quick Guide, n.d.; Watt, 2014)

References

Database design basics. (n.d.). Support.microsoft.com. Retrieved June 23, 2022, from https://support.microsoft.com/en-us/office/database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5#bmpurpose

DBMS — Quick Guide. (n.d.). Www.tutorialspoint.com. Retrieved June 23, 2022, from https://www.tutorialspoint.com/dbms/dbms_quick_guide.htm

Watt, A. (2014). Database Design — 2nd Edition. In opentextbc.ca: Vol. Chapters 4–7. BCcampus. https://opentextbc.ca/dbdesign01