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 who wants to take requirements from their customer in the business community and then produce a design and deploy it to a live database.
The design process will start from discussions with the consumers of the product data asset, in this case a database. The consumer will describe 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. May 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 logical data model adding Entities. We can add business definitions and any other business metadata that will help capture requirements from the Business. 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 and also 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 converting the generic data types to product specific types using user extensible mappings. The wizard will also resolve many-to-many relationships that are not valid in databases and generate indexes and views.
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, users 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 physical model 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.
Watch how we do it with ER/Studio
We have produced a series of videos which you can watch available in the IDERA Resource Center