Creating and Editing Tables

Go Up to Creating and Editing Entities and Tables

The table is the basic unit of a physical data model. ER/Studio Data Architect facilitates the smooth conversion of databases into physical models for deployment on an SQL database platform. You can populate your data model with tables by importing or reverse-engineering an existing database, generating a new physical model from a logical model, or by adding individual tables as needed. You can make changes to a table at any time using the Table Editor. You can also edit table Columns and Indexes using the Column Editor and Index Editor. respectively.

ER/Studio Data Architect lets you add as many tables to your physical model as you need. When you add a table to the diagram, it creates as an independent table with square corners in the Data Model Explorer. You can create relationships between tables and create views as needed. As you draw relationships and foreign Keys, associating the table with others, ER/Studio Data Architect automatically maintains the table status as an independent or dependent (child).

To define a Table

In the Data Model Explorer, expand a Physical model or submodel.
From the Data Modeling toolbar, click the Table tool, position the cursor over the desired location in the Data Model Window, and then left-click.
Right-click to revert to the Selection tool.
To define table details, double-click the table to launch the Table Editor.
Define the table as required, and then click OK.

Columns tab

Add, edit, or delete columns. You can also change the order of the columns in the list which changes their corresponding column placement in the entity's table. You can also add an column to the primary key for the table. The tabs at the bottom of the Columns tab apply only to the column selected and are the same as those available for the Attributes tab in the Entity Editor. For more information about this feature, see Creating and Editing Columns.

Databricks only. Column Masking is supported in the Columns tab. You can type in a function or select a function from the available list to use as the masking function for the column.

Dimensional tab

This tab is available for Dimensional Models only. Lets you set the dimensional type of the table and the data type.

Override Automatic Designation: If selected, the table's type will not be changed when the Automatic Table Type Identification process is run on a related table.
Run Automatic Table Type Identification: If selected, when you change the Dimensional Model Table Type and then click OK to close the Table Editor, ER/Studio Data Architect automatically changes the type of other tables to reflect the change, using the same logic as it does when creating a new dimensional model from a logical model. For example, if you change a table's type from Dimension to Fact, its parent's type can change from Snowflake to Dimension.

DDL tab

Displays the DDL to create the table. The DDL shown here includes code to reproduce the table including all its properties, as defined on the tabs of the Table Editor. You can edit the code if you like. You can customize the DDL that appears to include only include the options you want. For more information, see Customizing Table DDL.

Indexes tab

Add, edit, or delete indexes. Provides access to the Index Editor. For more information, see Creating and Editing Indexes.

Foreign Keys tab

View any relationships that exist between the selected table and other tables in the model. Information included about the relationships include the relationship type and the Foreign Keys. For more information, see Creating and Editing Keys.

Glossary tab

Displays a table consisting of all business terms related to the associated Table. Columns include Term Name, Glossary (parent glossary of the term), and Description.

Available options include:

Add Term. Launches the Business Term Selector dialog box, allowing users to relate/unrelate terms from the Object.
Remove Term. Unrelates the selected term from the Object.
Refresh Term. Starts caching of the terms in the background with the status displayed next to the button.

Business Term Selector dialog box

In this release of ER/Studio Data Architect, clicking Add Term in the Glossary tab launches the Business Term Selector dialog box, as shown in the following image. This feature allows users to relate and unrelate terms from an Object.

Glossary Filter. Allows you to limit search results to terms that are either children of or are related to the selected Glossary.
Search. Allows you to limit search results to terms that contain the search string in the term name. Note, this is pre-populated with the name of the ER object in context and can be changed by the user.
Also Search in Description. Toggles whether you want to include terms that contain the search string in their description.
Relate and Unrelate. Marks a term as related to or unrelated from an Object, and moves the selected term to or from the Related Terms area for the Object.
Apply. Sends a request to Team Server to update the related terms. Note, at this point the relationship is committed to Team Server.
Cancel. Discards any changes made without saving.
Create New. If you can't find an appropriate business term you can create a new one. This opens a dialog that allows you to specify the name, parent glossary, and description of a new term.

Definition tab

Enter or edit a definition for the table. If the target database supports it, ER/Studio Data Architect adds this definition as a table comment when generating SQL code.

It is also possible to create a term.

Note tab

The notes added here are included as part of the HTML report when you generate a report for the model. You can format the notes using standard HTML tags.

Where Used tab

Shows which logical and physical models and submodels implement the table.
Also shows the denormalization mapping history of the table. From the Where Used tab you can edit denormalization mappings and user-defined mappings. For more information, see Associate Similar Objects Using User-Defined Mappings and Viewing and Editing Denormalization Mappings.
It shows the related Team Server data source. For more information, see Relating models to ER/Studio TS Data Sources.

User-Defined Mappings tab

Shows any user-defined mappings (UDM) for this table. UDMs tie data together that can be conceptually related but not necessarily with concrete transactional relationships. UDMs are not used when comparing data models or when generating SQL. Use the Note tab to describe the relationship between entities that is created using user-defined mappings. For more information, see Associate Similar Objects Using User-Defined Mappings.

Storage tab

Lets you select various storage options depending upon the selected database platform. For more information, see Defining Table Storage.

Dimensions tab

Available for the IBM DB2 for LUW 9.x (and later versions) platform. This tab supports multi-dimensional clustering (MDC) tables, a table type that is advantageous in certain application areas. The MDC table uses block-based indexes and can be physically clustered on more than one key or dimension simultaneously.

Click Add to bring up the Add Dimension dialog where you can choose from a list of available columns in the table to add to a dimension. You can create multiple dimensions for a table.

You can add the columns one at a time by adding a column and then clicking OK and then clicking the Add button again on the Dimensions tab. This creates a single-key dimension.

You can also add the columns as a comma separated list by adding multiple columns on the Add Dimension dialog, changing their order as required and then clicking OK. This creates a multi-key dimension.

You should carefully consider the page and extent size of the table because once the table is created you cannot change the table storage parameters of an MDC table without dropping and recreating the tablespace.

Properties tab

MySQL only. Select the table type storage engine you want.

HEAP. Also know as Memory. Provides in-memory tables. Handles non-transactional tables.
INNODB. Provides transaction-safe tables.
MYISAM. Manages non-transactional tables. Provides high-speed storage and retrieval, as well as full text searching capabilities.

Partition Columns tab

Lets you select partitioning options for the selected table. Partitioning a table can enhance query and I/O performance. For more information, see Table Partition Columns for IBM DB2 for LUW 5.x, 6.x, 7.x, 8.x

Distribute Columns tab

For IBM DB2 for LUW 9.x (and later versions). Lets you select the columns to use for the distribution key, which can enhance query and I/O performance. For more information, see Distributing Columns for IBM DB2 for LUW 9.x (and later versions).

Distribution tab

For the Netezza, GreenPlum, and Amazon Redshift platforms. Lets you select the columns to use for the distribution key, which can enhance query and I/O performance. For more information, see Distributing Columns for Netezza, Distributing Columns for GreenPlum, and Distributing Columns for Amazon Redshift.

Organization tab

For the Netezza 6.0 (and later versions) platform. Lets you select up to four columns to use for the organizing key, which can enhance query and I/O performance. For more information, see Organizing Columns for Netezza.

Sort tab

For the Amazon Redshift platform. Lets you select the sort style for a table and, if the sort style is KEY, lets you select the columns to use for the sort key. For more information, see Sorting Tables for Redshift.

Partitions tab

Lets you select the columns to include in a partition and reorder the selections. Provides access to the Partitions Editor. For more information, see Partitioning a Table.

Overflow tab

Lets you specify how to store the overflow records when a row is updated and no longer fits on the page where it was originally written. For more information, see Define Table Overflow Options.

Constraints tab

Add, modify, or delete a constraint. Check constraints can ensure the validity of data in a database and provide data integrity. For more information, see Enforcing Data Integrity Using Check Constraints.

Dependencies tab

Add, modify, or delete trigger, function, and procedure dependencies that are bound to the table. These dependencies must be met before the data can be loaded into the table. Dependencies are SQL code such as functions, triggers, and procedures. This tab displays views that use the attributes/columns of current entity/table For information on Views, see Creating and Editing Database Views. For more information on other dependencies, see Creating and Editing Functions, Creating and Editing Triggers, and Creating and Editing Procedures.

Capacity Panning tab

Assists in planning the space required to accommodate database growth. For more information, see Planning for and Predicting Database Growth.

Permissions tab

Sets access roles and user permissions for the table. Keep in mind that the more specific permissions are, the more time you may have to spend maintaining and updating them. The Roles and Users must be previously assigned to appear in the View Editor. For more information, see Creating and Editing Database Roles and Creating and Editing Database Users.

PreSQL & Post SQL tab

Lets you enter SQL to be applied before or after the CREATE OBJECT statement. The PreSQL and PostSQL scripts entered here are included in the script when you generate the physical database.

Naming Standards tab

Controls User and Object Permissions to modify the affected item. When a Naming Standards Template is bound to an object, then that template is used instead of the Naming Standards Template selected in the Naming Standards Utility or the Generate Physical Model utility. This allows you to apply different naming standards to different objects when portions of a model require different versions of a standard. For example, some objects already exist in the database and you do not want to apply a newer naming standard to them. The Freeze Names option when selected prevents any naming standards applied from changing the name of the table selected.For more information, see Enforcing Naming Standards Using Naming Standards Templates.

Compare Options tab

Use this tab to override global compare options for a specific table. Select the differences you want the Compare and Merge Utility to ignore.

Data Lineage tab

Maps the flow of data from source to target tables and columns in the model. The Data Lineage tab appears in the entity and table editors. At the column level, you can add transformation logic for source/target column mapping. If a Data Movement Rule has been bound to an entity, you can override the bound value from this tab by double-clicking the row containing the Dave Movement Rule and then entering the new value in the Value Override Editor. For more information, see Data Lineage Workflow and Documenting Table/Entity Data ETL.

Security Information tab

Displays the current security information as defined in the Data Dictionary. You can add or edit security reporting information in by double-clicking the field you want to change. To set the model-wide default value for a property, go to the Data Dictionary tab and expand the Data Security Information Node. For more information, see Enforcing Security Using Data Security Types and Properties.

Extended Properties tab

Allows you to manage SQL Server extended properties stored on the table in the database. You can add, edit, and delete property values and descriptions.

Attachment Bindings tab

Bind an external piece of information, or attachment to the table. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary and must be applied to the table before they will display on this tab. For more information, see Attaching External Documents to the Data Model.

Notes

A new a table automatically appears as an independent table with square corners. As relationships are drawn associating the table with others, ER/Studio Data Architect automatically maintains its status as an independent or dependent table.
You can name tables as you add them by using on-screen editing. Either type a name for a table immediately after adding it to the diagram, or press the SHIFT key, click the default table name and then type a name. Click the diagram background to save the name and deactivate on-screen editing. For information on table naming rules and conventions, see Best Practices.
The status bar at the bottom right of the application window displays the number of entities in the selected model.
Specify naming rules, such as maximum length, case, and synchronization level between entities and tables on the Logical and Name Handling tabs of the Options Editor, which you can access by choosing Tools > Options > Logical.
Specify the display order of the attributes and primary keys of any entity on the Display tab of the Options Editor, which you can access by choosing Tools > Options > Logical.
You can customize the information that pops up when you hover the mouse over an entity by selecting an option from the list that appears when you choose View > Cursor Popup Help Options > Entity Help.
By default, ER/Studio Data Architect automatically fits entities and views to display all content selected. However, you can resize any entity to any size by selecting the entity and then dragging the size handles. You can make global changes to how entities display by right-clicking the model, and selecting Diagram and Object Display Options. Then you can choose the display options desired in the Entity and View tabs of the Diagram and Object Display Options editor.
For information on arranging tables in the Data Model Window, see Changing Data Model Layout.
For information on changing the colors of tables, see Overriding Color and Font Settings for a Specific Object.