Home > Data Modeler Concepts and U... > Approaches to Data Modeling
When modeling data, you can choose an approach best suited to the nature of the work to be done. The approaches to data modeling include the following: designing a new database, developing a design for an existing database, or performing maintenance on an existing database design
Top-Down Modeling: for designing a new database
Bottom-Up Modeling: for creating a database based on extracting metadata from an existing database or using the DDL code obtained from an implementation of an existing database
Targeted Modeling: for adapting a database to new requirements
Top-down modeling gathers information about business requirements and the internal environment, and proceeds to define processes, a logical model of the data, one or more relational models, and one or more physical models for each relational model. The steps and information requirements can range from simple to elaborate, depending on your needs. Top-down modeling can involve the following steps, but you can abbreviate or skip steps as appropriate for your needs.
Develop the business information.
Create documents. In the object browser, right-click Logical and select Properties, then click Documents and add items as appropriate.
Create responsible parties with contacts, e-mail addresses, locations, telephone numbers, and locations. In the object browser, right-click Logical and select Properties, then click Responsible Parties and add items as appropriate.
Define any other information. In the object browser, right-click Logical and select Properties, then modify other properties (Naming Options, Comments, Notes) as needed.
Develop the process model, using a data flow diagram. In the object browser under Process Model, right-click Data Flow Diagrams and select New Data Flow Diagram.
Create processes. For each process, click the New Process icon, click in the data flow diagram window, and enter information in the Process Properties dialog box.
Create external agents. For each external agent, click the New External Agent icon, click in the data flow diagram window, and enter information in the External Agent Properties dialog box.
Create information stores. For each process, click the New Information Store icon, click in the data flow diagram window, and enter information in the Information Store Properties dialog box.
Create flows with information structures. For each flow, click the New Flow icon, click the starting object (such as a process) in the data flow diagram window, and click the ending object for the flow; then double-click the flow arrow and modify information (as needed) in the Flow Properties dialog box
Develop the logical model.
Create entities, and for each entity its attributes and unique identifiers. You can create all entities first and then the attributes and unique identifiers for each, or you can create the first entity with its attributes and unique identifiers, then the second, and so on.
To create an entity, click the Logical tab, click the New Entity icon, click in the logical model window, and enter information in the Entity Properties dialog box. You can also enter attributes and unique identifiers using the appropriate panes in this dialog box.
Create relations between entities. For each relation, click the desired icon: New M:N Relation (many-to-many), New 1:N Relation (one-to-many) , New 1:N Identifying Relation (one-to-many, identifying), or New 1:1 Relation (one-to-one). Click the entity for the start of the relation, and click the entity for the end of the relation; then double-click the relation line and modify information (as needed) in the Relation Properties dialog box.
Apply design rules to the logical model. Click Tools, then Design Rules, and use the Design Rules dialog box to check for and fix any violations of the design rules.
Forward engineer the logical model to a relational model. Click Design, then Engineer to Relational Model, and use the Engineering dialog box to generate a relational model reflecting all or a specified subset of objects from the logical model.
Develop the multidimensional model, if needed.
Create cubes.
Create levels.
Create dimensions.
Create links.
Apply design rules for the multidimensional model.
Export the multidimensional model, as needed.
Develop one or more relational models, doing the following for each as needed.
Split tables. To split one table into two, select the table on the relational model diagram, and either click the Split Table button or click Object, then Relational, then Split Table.
Merge tables. To merge a table into another table (removing the merged table), either click the Merge Table button or click Object, then Relational, then Merge Tables. Then, in the relational model diagram, first the table into which to merge columns from the other table, and next select the other table whose columns are to me merged. (After the merge, the second table will be removed.)
Check design rules for the relational model. Click Tools, then Design Rules.
Develop one or more physical models for each relational model, doing the following for each.
Open a physical model.
Check design rules for the physical model. Click Tools, then Design Rules.
Generate DDL code, which can be used to generate the actual database objects. Click View, then DDL File Editor, and then use the DDL File Editor dialog box to select a physical model, generate DDL code, and save the code to a script file.
Bottom-up modeling builds a database design based on either metadata extracted from an existing database or a file with DDL code that implements an existing database. The resulting database is represented as a relational model and a physical model, and you reverse engineer the logical model from the relational model. Bottom-up modeling can involve the following steps, but you can abbreviate or skip some steps as appropriate for your needs.
Generate the relational model in either of the following ways:
Extract metadata directly from an existing database: click File, then Import, then Data Dictionary; then follow the instructions for the wizard (see Data Dictionary Import (Metadata Extraction)).
Import DDL code that reflects an existing database implementation. Click File, then Import, then DDL File.
As needed, modify the relational model and create additional relational models.
As needed, denormalize the relational model or models. Perform the following steps iteratively, as needed, on each model.
Split or merge tables, or do both.
To split one table into two, select the table on the relational model diagram, and either click the Split Table button or click Object, then Relational, then Split Table. Use the Split Table wizard to copy or move source foreign keys and columns to the target table (the new table to be created).
To merge a table into another table (removing the merged table), either click the Merge Table button or click Object, then Relational, then Merge Tables. Then, in the relational model diagram, first click the table whose columns are to be merged into the other table, and next click the table into which to merge the columns from the first table that you clicked. (After the merge, the first table that you clicked will be removed, and the remaining table will include its original columns plus the columns that had been in the first table.)
Check the design rules for the model. To view the design rules, click Tools, then Design Rules; select the desired relational model; and use the Design Rules dialog box.
Reverse engineer the logical model from a relational model. Click the Engineer to Logical Model icon, or click Design, then Engineer to Logical Model.
As needed, modify the logical model.
Check design rules for the logical model. Click Tools, then Design Rules.
Save the design.
Generate DDL code, and use it to create the database implementation. Click View, then DDL File Editor. In the DDL File Editor dialog box, select the physical model and click Generate. Specify any desired DDL Generation Options, then click OK.
Targeted modeling involves maintaining an existing database by adapting it to new requirements.
Note: Maintaining a database with Data Modeler requires that the design and the actual database implementations be fully synchronized. If you are not sure if this is the case, you should consider the designs outdated and perform the procedures in Bottom-Up Modeling. |
Depending on the kind of changes necessary, you can start with the logical model, one or more relational models, or one or more physical models, and then forward engineer or reverse engineer as appropriate.
To start with changes to the logical model:
For each logical model object (entity, attribute, relation, and so on) that you want to modify, modify its properties. For example, to add an attribute to an entity:
Double-click the entity's icon in the Logical diagram (or right-click the entity name in the object browser and select Properties).
In the Entity Properties dialog box, click Attributes.
Click the Add (+) icon and specify the attribute properties.
When you are finished modifying the logical model, forward engineer the changes to the relational model or models by clicking the Logical pane and clicking Design, then Engineer to Relational Model.
In the Engineering dialog box, specify any desired filtering, then click Engineer.
To start with changes to a relational model:
For each relational model object (table, column, and so on) that you want to modify, modify its properties. For example, to add a column to a table in a relational model:
Double-click the table's icon in the diagram for the relational model (or right-click the table name in the object browser and select Properties).
In the Table Properties dialog box, click Columns.
Click the Add (+) icon and specify the column properties.
When you are finished modifying the relational model, reverse engineer the changes to the logical model by clicking the pane for the relational model and clicking Design, then Engineer to Logical Model.
In the Engineering dialog box, specify any desired filtering, then click Engineer.