Some developers like to have a clear separation between the logical analysis and physical design of an application system. SQL Developer Data Modeler supports this separation of tasks and also supports the synchronization of metadata between these. This means that you can start new application development with a logical model without needing to think about the physical implementation details. Once you have completed the analysis, you can then transform the entities to tables and work on the physical implementation. SQL Developer Data Modeler also allows you to work on the relational model with its tables, views, and constraints without needing to consider the physical detail. Without the physical detail, you can create a relational model that is database-agnostic. Then, for a single relational model, you can create multiple physical implementations and generate different DDL for each of these.
In this section, we'll start with a logical model, transform this to a relational model, and then look at the physical details before generating the DDL.
While some believe that the only way to start data modeling is with conceptual analysis, others believe that this extra layer of work is not necessary. Therefore, they skip the high-level conceptual analysis and start by building the data model. SQL Developer Data Modeler supports these alternatives and provides the option to synchronize these models.
This is often considered to be the most traditional approach to database design and generation and falls into the waterfall approach of strategy, analysis, design, and generation. The mindset behind this approach is that the more work you do up front, the better. Early changes to the design are less costly than they are likely to be later on. Liken this to building a house. If you plan to change the position of the bathroom while the house is still in the drawing and architectural phase, it's much less costly than planning to move it once the building work has begun. The further down the line the changes are requested, the more costly the decision.
The flow of work in this approach is to build the logical model, set standards and build the glossary, and then transform or forward engineer it to the relational model. Once you have the relational model, review and make adjustments as required. At this stage, you can add extra columns, rename columns or tables, and add constraints. Once the relational model is sound, you can create the additional physical detail as needed. Generate the DDL and review the scripts when the relational and physical models are complete.
If you already have an application built, the database will be in place. Being able to visualize the model allows you to view and extend the mode or migrate the details to a new database. Starting with the schema supports migrating to new environments or upgrading applications. In the current climate, consolidating applications is important. Therefore, being able to review the models for these existing applications means that you can make decisions before embarking on updates.
Some teams want to model their databases but feel that the ERD is an unnecessary layer. Therefore, they start by building a schema model. At this level, you are building tables, columns, and constraints. You can start by importing tables from an existing database and then augment them by adding new tables and modifying the imported tables. This is a valid approach to modeling, but it does mean that you are already thinking about the implementation details when you start designing.
A logical ERD is made up of entities and attributes. You can identify unique identifiers and relationships between entities. You are not tied down to the implementation details at this point. Therefore, you can create a many-to-many relationship or supertypes (if they support the business requirement). In essence, you are using an ERD to capture the business needs:
Select the Logical node in the object browser and select Show from the context menu to create or open a blank page for your logical model.
To start creating the model, use the set of buttons on the toolbar, as shown in the following screenshot. The button is "sticky" by default. This means that you can draw an entity, which invokes the property dialog. Then, on closing the dialog, you are immediately ready to draw the next entity without needing to select the button again:
As soon as you have drawn the shape, the Entity Properties dialog is invoked, allowing you to populate the details.
You can provide a broad spectrum of details for each entity, including documentation details, such as change request details and comments.
General entity properties include adding synonyms and abbreviations. Synonyms are for documentation purposes and can be a part of your glossary. They are not database synonyms.
Note
Singular entity names and plural table names
Typically, entity names are singular and table names are plural. Some products allow you to set this plural value under the entity properties, and the plural value is then used when engineering to create the table. You can use the Preferred Abbreviation field for this purpose in SQL Developer Data Modeler. If you want to use the preferred abbreviation, you will also need to set the Use preferred abbreviation in the forward engineering General Options dialog.
Once you have entered the main details for the entity, you can add the required attributes. In the following screenshot, you can see the basic requirements for each attribute, such as setting the data types and the unique identifier properties. To add more details to each attribute, click on the Properties button or double-click on the required attribute:
The Entity Properties dialog includes a Relationships node with a list of all the available relationships. Double-click on the name of the relationship to drill down to the detail. The General detail is useful when you need to review, as shown in the following screenshot. If you reverse engineer the details from a relational model, the name on the source and the target are blank. Completing this detail and displaying it on the model is very useful for later documentation when using the model as a discussion point:
In the earlier section on syntax, we mentioned the Cardinality, Optional, and Identifying properties on the diagram. These properties are set in this dialog.
You can invoke the same dialog by selecting the relationship in the diagram and invoking the Properties dialog.
A domain is a defined data type that may have associated valid values. By defining domains, you can reuse them throughout the model, thus providing consistency to the model. For example, you can create a Yes/No domain and set the data type and associate valid values to Yes and No. Once this is created, you can associate it with all the columns or entities that use the Yes/No values. Domains need not be associated with valid values. Instead, you can just set the data type and then apply the domain to columns or entities. You can work with domains and default values independent of the ERD or Relational models. If you set these at the attribute level and then forward engineer, the detail is carried forward to the tables and columns. However, these can be easily added directly to the columns.
When setting data types for attributes or columns, you have a choice of categories, such as:
- Domains
- Logical
- Distinct
- Structured
- Collection
The most common category from the aforementioned ones is the Logical type, where you can set the type and scale for the data type. When working with a large number of entities and attributes, using domains is more efficient, as any changes made to the domains are propagated to all the columns or attributes that use them. SQL Developer Data Modeler provides a default domains file. All the domains that are added to this file are available for all the designs that you work on. Alternatively, you can create a domains file that can be used with specific designs.
A user-defined distinct type is a data type derived from an existing logical type, defined under Tools | Data Modeler | Types Administration.A distinct type shares its representation with all default data types, but is considered to be a separate and can also be named as per our choice.Structured types are supported as named user-defined composite. A structured type can be defined based on a basic data type, a distinct type, another structured type, or a reference to structured type, or it can be defined as a collection type.Collection types represent arrays or collections of elements (basic type, distinct type, structured type, or another collection). You can create new collection types or edit the properties of existing collection types.
Navigate to Tools | Domains Administration and create your own new domain by clicking on Add. This action adds a new domain to the default domains file. When adding domains, provide the name and the Logical type details and add the details such as size and precision. You can also remove or modify domains using this dialog, as shown in the following screenshot. The changes that were made here are maintained separately from your design. Therefore, you can enforce standards across designs:
You can create a separate domains file that is associated with your design by clicking on the Select button in the Domains Administration dialog. This action allows you to create or open a separate domains file. Once it's created, add new domains to the file as previously described. To use these domains in a design, navigate to File | Import | Domains. When you save the design, these domains are then saved with the design.
Domains offer more than just providing standard data types. They can also contain valid values and thus allow you to use the domain to:
- Implement a check constraint
- Enforce a range of values
- Provide a set of valid sets
In the preceding screenshot, we added a value list. When adding check constraints to domains, you can add constraints specific to a database version, or you can choose to create a generic constraint, which will be applied to any database that you choose.
SQL Developer Data Modeler supports forward and reverse engineering. These transformation tools support:
- Forward engineering from an ERD to a new or an existing relational model
- Reverse engineering from a relational model to a new or an existing ERD
When engineering to an existing model, you can choose whether you want to include or exclude changes made in the different models.
SQL Developer Data Modeler supports multiple relational models for each logical model. Irrespective of whether you are starting with a logical model, or the logical model is derived from an existing relational model, you can forward engineer to a new relational model. Create an empty relational model by selecting New Relational Model from the Relational Models context menu in the object browser. This automatically opens a new blank relational model. Open the Logical model and select the Engineer to Relational Model button (
):
Once the dialog opens, you can use the drop-down list of relational models to determine which model you want to update or create, as follows:
This is the reverse action of the previously described forward engineering option. In this case, you use a relational model to create or update a logical model. If you already have a model, the decisions that you need to make revolve around layout, and the adding, deleting or updating of items. Reverse engineering relational to logical models is very useful when you are doing a "bottom-up design". In other words, you have imported the table definitions from an existing script or data dictionary. Once the relational model has been created and refined, reverse engineer the model to create a logical Entity Relationship Diagram.
The relational model is made up of tables and views with their columns and relationships. The details for these items are listed in the object browser under the Relational Models node. These are also the only details that are displayed in the diagram, as shown in the following screenshot. Details such as tablespaces, triggers, or physical properties are listed in the physical model:
The physical model is the source for the DDL scripts. Therefore, it contains many more details than are available on the diagram. If you start from the logical model and engineer forward to the relational model, you'll need to start building the physical model from scratch. It will initially only be populated with the details in the relational model. If you are working from an existing database model, then importing the schema also imports the physical model.
SQL Developer Data Modeler supports multiple physical models for each relational model. This is useful if you want to build different DDL scripts for different databases (for both Oracle and non-Oracle databases) or the test, development, and production databases in Oracle. In the next section, we'll look at ways to:
- Import a schema from the Data Dictionary
- Create a new physical model
Importing from a database connection imports objects such as tables, columns, and views and places them in the relational model diagram. You can also import physical properties such as tablespaces, roles, and directories. These details are listed under the physical node in the object browser. The Data Dictionary Import Wizard lets you select the objects and object types that you can import, as shown in the following screenshot:
All of the preceding work culminates in producing the DDL scripts to create or update a schema design. Invoke the DDL Generation dialog by navigating to File | Export and then, select the model of your choice. You can also click on the Generate DDL button in the toolbar to invoke the DDL File Editor dialog.
With the correct database selected, click on Generate to invoke the DDL Generation Options dialog:
By default, all objects are marked for generation. Thus, you can work through the lower tabs and remove the items that you don't want in the DDL.
Use the Tree View tab to generate a DDL for a specific schema or for any of the objects that are not assigned to the users, as shown in the following screenshot. For example, if you have associated various objects with the HR schema in the physical model, then you can generate all the DDLs for the HR schema. To generate schema-specific DDL, select the Assigned to Users node in the tree and expand the required user; in this case, it's HR.
The top set of tabs drives additional text within the scripts. We mentioned naming standard templates and resetting object prefixes earlier; here we have another opportunity to switch the names, either for certain object names, or to switch out a prefix and replace it:
The Data Modeler Viewer extension to SQL Developer also allows you to create models of the tables, views, and constraints in the database connections that you have access to. To create a new model, expand the database connection in the Connections navigator and then, select the tables that you want to include in a diagram. Drag the selection on the relational model.