Question: Think back to Module 01 when we discussed the importance of planning a database. Just like building a home, it is important to have a plan and blueprint documented before even laying the foundation. Databases require the same approach. You will need to plan and build a blueprint or Enterprise Relationship Diagram to determine the structure of the database.
Before you even begin creating a diagram, you first need to know what you're trying to accomplish with the database. Some database-planning tasks involve interviewing or surveying users, evaluating business processes, and developing use cases. User surveys can be performed to determine how the database will be used and what type of data will be stored in it. Some sample questions are:
How many records do you need to be stored?
What information do you plan to store?
Will you need to add or update information? How often?
How many users would need to access the database?
What kinds of reports will you need?
Evaluating business processes may involve creating a process flow map to document business activities from beginning to end. All processes that interact with the database should be considered and you should document what information these processes need.
A use case is a description of how the database should function based on any inputs or queries. Adding or updating records would be an example of a use case.
Once the planning tasks have been completed, you're ready to begin creating your blueprint, the entity relationship diagram or ERD. This is a logical representation of the database or also called a model. Just like an architectural blueprint for a home, the ERD will serve as your model for the database. The ERD does not store data, and you can't retrieve data from it. It is simply a model.
Microsoft Visio is a popular tool used to create ERD diagrams. Below is a sample ERD for the AdventureWorks sample database.