Database Primer - ER Model
For those of you who have worked with database design, this will come as a refesher. For everyone else, you've been using databases for years without realizing it! Most everything we do in our society is directly linked to database modeling. When you swipe your card at the gas station, you are engaging with a database. When you log in to a website (perhaps Blogger!), you are engaging with a database. I challenge my readers to find an instance in modern society where a database is not involved. Chopping wood in an isolated cabin in the woods doesn't count! It will be a tough one! So let's talk about databases by going over the Entity Relationship (ER) database model.
The entity database relationship model helps tie together the entities, their attributes, and the relationships between them. For example, if a company wanted to track their sales they would need to identify the entities of Product, Customer, and Order. Among those entities you would need attributes such as a Customer ID, Product ID, and Order Status. This helps the many facets of a business come together in a seamless and efficient manner. While invaluable for the day-to-day operations of businesses, ER database designs have a few limitations. These limitations include complexity, time-based analysis, and standardization challenges.
The limitation of complexity arises when an ER model attempts to use complex relationships such as many-to-many relationships. An example of a many-to-many relationship in the book was that a Student can be in many Classes, and each Class can have many Students (Coronel & Morris, 2017). This can create a confusing relationship as represented in your ER design. The solution to this limitation is to break these entities down further until you have a relationship model of One to Many or 1 to 1. However, this poses complexity creep issues as your ER Database will soon become sprawling.
Additionally, ER models are not well suited for representing time in both trend analysis over time and historical data as they are not logging that information. The Entity Relationship model deals with Entities, Attributes and their Relationships but not Time. If you wanted to extrapolate your sales forecast from historical sales history data, you would need to extract from your ER into a more appropriate software solution. Furthermore, if you aren't including an attribute of time such as Order Date or Order Time, you will have trouble extracting even that information to a more suitable program or solution. Thus, the solution for this involves ensuring you include a time element wherever possible as an entity attribute.
Finally, standardization can pose a problem due to both evolving business needs and turnover. Since your database is conceptualized and implemented in a static nature, any changes requested by the business will cause disruption in your standardization process. Additionally, if an employee who manages the database is off-boarded from the company, there may be a lack of understanding from their replacement on how the database was conceptually designed in respect to the relationships between the entities and attributes. Proper documentation while the database is being designed can help this last point as onboarding database engineers can properly identify the goals, design philosophy, and relationships between attributes and entities without having to waste too much time getting up to speed.
These limitations will directly influence both your initial design and the iterative process to which you adapt and compromise. Your database will need to correctly access at the time of its creation the goals of the stakeholders involved, while also adapting to changing goals. If your business shifts their model to a new customer need, you will need to incorporate that and adjust your database to ensure minimal redundant data, null data, and conflicting data. Time should be incorporated as an entity attribute wherever possible to enable both historical data utilization and trend analysis. Documentation is key in this process not only to serve as a guiding light to new eyes who are tasked to work on it, but as a reminder and pulling force to adhere to your stated goals.
References
Coronel, C., & Morris, S. (2017). Database Systems: Design, Implementation, & Management.
The entity database relationship model helps tie together the entities, their attributes, and the relationships between them. For example, if a company wanted to track their sales they would need to identify the entities of Product, Customer, and Order. Among those entities you would need attributes such as a Customer ID, Product ID, and Order Status. This helps the many facets of a business come together in a seamless and efficient manner. While invaluable for the day-to-day operations of businesses, ER database designs have a few limitations. These limitations include complexity, time-based analysis, and standardization challenges.
The limitation of complexity arises when an ER model attempts to use complex relationships such as many-to-many relationships. An example of a many-to-many relationship in the book was that a Student can be in many Classes, and each Class can have many Students (Coronel & Morris, 2017). This can create a confusing relationship as represented in your ER design. The solution to this limitation is to break these entities down further until you have a relationship model of One to Many or 1 to 1. However, this poses complexity creep issues as your ER Database will soon become sprawling.
Additionally, ER models are not well suited for representing time in both trend analysis over time and historical data as they are not logging that information. The Entity Relationship model deals with Entities, Attributes and their Relationships but not Time. If you wanted to extrapolate your sales forecast from historical sales history data, you would need to extract from your ER into a more appropriate software solution. Furthermore, if you aren't including an attribute of time such as Order Date or Order Time, you will have trouble extracting even that information to a more suitable program or solution. Thus, the solution for this involves ensuring you include a time element wherever possible as an entity attribute.
Finally, standardization can pose a problem due to both evolving business needs and turnover. Since your database is conceptualized and implemented in a static nature, any changes requested by the business will cause disruption in your standardization process. Additionally, if an employee who manages the database is off-boarded from the company, there may be a lack of understanding from their replacement on how the database was conceptually designed in respect to the relationships between the entities and attributes. Proper documentation while the database is being designed can help this last point as onboarding database engineers can properly identify the goals, design philosophy, and relationships between attributes and entities without having to waste too much time getting up to speed.
These limitations will directly influence both your initial design and the iterative process to which you adapt and compromise. Your database will need to correctly access at the time of its creation the goals of the stakeholders involved, while also adapting to changing goals. If your business shifts their model to a new customer need, you will need to incorporate that and adjust your database to ensure minimal redundant data, null data, and conflicting data. Time should be incorporated as an entity attribute wherever possible to enable both historical data utilization and trend analysis. Documentation is key in this process not only to serve as a guiding light to new eyes who are tasked to work on it, but as a reminder and pulling force to adhere to your stated goals.
References
Coronel, C., & Morris, S. (2017). Database Systems: Design, Implementation, & Management.
Comments
Post a Comment