Database modeling (i.e. the description of tables a database will consist of and how they will interact) is the most important stage in developing information systems and database applications.
A high-quality model increases the system performance, minimizes the superfluity of stored information and ensures a lower cost of the system maintenance.
DTM Data Modeler is an easy-to-use data modeling tool. Based on the IDEF1X methodology, it supports the classic entity-relationship logical modeling. Also the data modeling tool allows you to work with the physical components of a model: tables, views, indexes, procedures, etc.
Besides the classic modeling and creating a model-based database schema, the tool offers the reverse engineering feature, i.e. building a model based on the database schema that already exists in an existing database. The reverse engineering mode can create a whole model as well as to add new tables and other objects to the existing model.
Let's explain how to create a simple model with three entities: products, orders and order details. In the first step, you must create independent entities. In our case, they are orders and products. After they are created and added to the model, you should define the fields that will identify entries in these tables, i.e. their primary keys.
After the parent entities are created, you can start creating the child entity - order details. This entity depends on both parent entities because it both contains a product and is part of some order at the same time.
Relationships and Keys
When the entity is created, you should create relationships between the parent and child entities. In our case, we must use an identifying relationship because an order item makes no sense when it has no information about the product and when it has no reference to the order where it is contained.
When you create an identifying relationship, "keys migration" occurs, i.e. the primary key items of the parent entity become the primary key items of the child entity. It is a very convenient mechanism making it easier to create complete data models. However, if you do not need this mechanism for some reason, the tool allows you to disable it. Moreover, the program has a special mode that allows you to modify migrated fields manually.
After tables, keys and relationships are created, we can create other data field definitions and indexes and triggers related to the created tables. Right after that or at the same time, views and stored procedures are created if necessary.
We recommend that you specify the connection to the target database while you are creating the model. It will allow the program to get the list of supported data types and offer you to use the native type names while creating the physical model. Please note that the program allows you to create custom data types and use them in modeling. Of course, the support of this feature by the target database is required.
The "areas" feature is very useful when you work with a more complex model that contains a few dozens or hundreds of tables. An area is a read-only subset of the model that the program shows in a separate window. You can create a few areas by function groups the tables are related to. For example: accounting, sales, customers, etc.
Forward Engineering: Database Creation
Let's create a database for our model. If the connection is already established, select the "forward engineering" command from the menu or click the toolbar button. There are four database schema generation methods: all objects, new only, recreate or alter mode. Also, you can select objects to be generated. Before executing the command, the program shows you the generated script and allows you to edit it or save it to a file.
There are important options and settings that control the creation of the output script: the default mode (NULL or NOT NULL) for the target database, the statement separator, the comment mode, etc.
So we've created a data model and generated a model-based database schema. You may want to populate this DB with test data for further work. Please consider using DTM Data Generator for this purpose.