ER/Studio has two primary use cases: designing databases and documenting databases. In this blog, we will discuss designing a database.
In this use case, the mission is for a Data Architect to take requirements from their customer in the business community and produce a design to deploy to a live database.
The design process begins with discussions with the consumers of the product data asset, in this case, a database. The consumer describes the content of the data asset in natural language using business terminology.
Our approach is to craft a model starting with Business Data Objects in ER/Studio. These are a simple list of concepts. Many clients will create formal Conceptual Data Models using logical entity relation diagrams or even use our Business Architect tool, where concepts can be attached to business processes.
We then expand the Business Data Objects to a 論理データモデル by adding Entities. We can add business definitions and any other business metadata that will help capture requirements from the Business. The Data Architect can formalize these business metadata using the extensible user-defined properties.
At each stage, we can confirm the design with stakeholders.
Next, we attribute the entities, defining the information that characterizes the entities. We set the data type of these attributes using a set of generic ANSI types. One or more of these attributes can be defined as the Primary Key of the entity along with candidate or alternate keys.
We then draw relationships between the entities, including identifying and non-identifying relationships, as well as super/subtypes.
We now have a logical design that can be agreed without ambiguity with the stakeholders. This now needs to be converted to a physical design aligned with the target data product. Each product has a different set of rules, and ER/Studio allows you to generate a model from the logical and apply those rules.
ER/Studio provides a wizard to guide the user through this process with transformation options. This wizard performs a number of functions, such as:
An important part of the transformation is with naming. Most organizations wish to maintain standards for naming whilst using names compatible with the database product. ER/Studio can apply naming conventions and generate physical names using a Naming Standards Template. This naming process can be deployed and retained against the logical model or generated by the wizard during the physical model generation process.
The physical model can then be enriched with database-specific artifacts such as functions, procedures, user roles, etc. Once complete, the model can be deployed as DDL code to a database via the next wizard.
As requirements change, we can understand those changes and deploy them to the same database. This process occurs starting from the logical model and continues through. ER/Studio contains tools to connect to change management tools like Jira and create Named Releases. As the logical model changes, the 物理モデル can be updated using the Compare/Merge tool. Likewise, as the physical model changes, it can be compared with a live database to generate ALTER scripts to modify the database schema.
