You put your organization's naming rules into a dictionary, assign it to a connection, and make typing only a column's logical name (for example, Order Datetime) in an ERD set both the standard physical name (ord_dtm) and the type. For columns that already exist in the database, you compare them with the dictionary to find mismatches and decide yourself whether to fix them. It takes about 30 minutes.
Which assets you can assign depends on the project. Follow along either in a local project in the desktop app without signing in, or in an account project while signed in. If you do it in a local project while signed in, the dictionary you create in the manager becomes an account asset and does not appear in the assignment list ('What local projects can assign' in Data dictionary).
Before you start
- You need a connection to the demo store database and an ERD on the connection (Document an existing database as an ERD).
- First read how words decide names, domains decide types, and how a combination dictionary reads logical names (Data dictionary).
- These are the domains and words used in the examples. If your organization has its own standard, follow along with that instead.
| Domain | Type |
|---|---|
| Amount | DECIMAL(15,2) |
| Datetime | DATETIME |
| Flag | CHAR(1) |
| Number | VARCHAR(20) |
| Word | Abbreviation | Domain | Relations |
|---|---|---|---|
| Order | ORD | ||
| Customer | CUST | ||
| Datetime | DTM | Datetime | Synonym Date |
| Number | NO | Number | |
| Amount | AMT | Amount | |
| Use | USE | ||
| Flag | YN | Flag | |
| Start | STRT | Antonym End | |
| End | END |
Creating a domain pack
- In the app menu, open the item and click the item. Result: The data dictionary manager opens.
- Click the New button and choose the Domain item in the Type field. Enter
Demo Domainsin the Name field and click the Save button. Result: A domain pack to hold the type rules is created. Without signing in it is an asset on the device; when signed in it is an asset in your account. - Add the four domains in the table above with the Add button on the Domains tab, then click the pack's Save button. Result: The four domains are saved in the pack.
The domain fields and DBMS-specific types are covered in Domains. If you will use an official pack provided by NeoSQL as is, skip this step and choose the official pack as the dictionary's domain pack in the next step.
Creating a dictionary and adding words
- Click the New button and choose the Dictionary item under the kind (1).
- Choose the Combination item under the dictionary type (2), enter
Demo Dictionaryas the name and save. Result:Demo Dictionaryis created as a combination dictionary. The dictionary type cannot be changed later. - In the dictionary's Domain Pack field, click the Select domain pack button and choose
Demo Domains. Result: The domain pack the dictionary uses is set toDemo Domains. - Add the words in the table above with the Add button on the Words tab. For each word, fill in the Word field, the Abbreviation field and the Domain field; give Datetime the synonym
Date, and give Start the antonymEnd. Result: An antonym is recorded on both words. - Click the dictionary's Save button. Result: One dictionary now holds and saves the rule for reading a logical name as words and joining their abbreviations, and which domain each word uses.
The word fields, and the difference between synonyms, antonyms and related words, are covered in Words.
Assigning it to a connection
- In the project's explorer, right-click the
shop-dbconnection and choose the menu. Result: The connection dialog opens on the Data Dictionary tab. - Click the Select Dictionary button (5) and choose
Demo Dictionary. For an account asset, choose a version as well. Result: TheDemo Dictionarycard appears in the dialog. - Check that
Demo Domains, set by the dictionary, came along into the Domain slot (2). - Click the dialog's Save button. Result: ERDs and table tabs on the connection now read logical names with the dictionary. A connection sees the dictionary as it was when assigned, so later edits to the dictionary do not change the connection by themselves (Assigning to a connection).
Assignment is per connection, not per project. Other connections in the same project can have a different dictionary or none.
Creating new columns from logical names
- Open an ERD of
shop-dband click the Logical mode button in the info panel. Result: The Logical Name cell appears in the column grid, and because the connection has a domain pack assigned, the Domain cell appears too. - Place a new table with the Add New Table button and type
Customer Orderinto the logical name field in the table header. Result: The table name changes tocust_ord. The dictionary splits the logical name into words and joins their abbreviations with_, following the connection DBMS's case rules. - Add a column with the grid's + button and type
Order Datetimeinto the Logical Name cell (1). Result: The physical name (2) becomesord_dtm, the domain (3) becomes Datetime, and the type (4) is filled in from the domain. - Add
Order Number,Customer Number,Order AmountandUse Flagthe same way. Result: If there is one domain candidate from the last word, it is assigned right away; if there are several, it remains as a grey suggestion in the domain cell. Click the check mark in the cell to assign the suggestion. - Try typing a synonym, such as
Order Date. Result: The logical name changes to the standard wordsOrder Datetime, and the physical name becomesord_dtm.
- The columns of a new table do not exist in the database yet, so their names and types change right away. When you are done, create the real table with Apply changes to the database.
- If you edit a value the domain decides (type, length, nullability and so on) directly in the column, the column's domain assignment is removed.
- On a DBMS that allows only one auto-increment column per table, if another column is already auto-increment, the domain's values are applied without its auto-increment.
- If a column with the same name already exists, the physical name is not changed and the Could not rename to ord_dtm. A column with that name already exists notification appears.
Reading the marks in the logical name cell
What the dictionary read stays as marks in the logical name cell. The marks are not saved; they are recalculated from the dictionary every time, so they follow when you edit the dictionary.
| Mark | Meaning |
|---|---|
| Wavy underline | The characters are not in the dictionary. They were left out of the physical name |
| Dotted underline | A synonym or forbidden word was read as the standard word |
| Italic text | The physical name the dictionary would build differs from the column's current name |
Hover over a cell with a mark and an icon appears at its end. Clicking it opens the Review needed menu, which shows the affected words in a table with the New, Mismatch, Related and Antonym columns, and offers items to fix them.
Comparing existing columns with the dictionary
For a column that already exists in the database, typing a logical name changes neither its name nor its type. This keeps documentation from piling up changes waiting to be applied, or from truncating data by shortening a length. Instead, mismatches are marked, and you decide for each column whether to fix it.
- Select
ordersin the ERD and typeOrder Datetimeinto the Logical Name cell of theordered_atcolumn. In logical mode the column name column is hidden by default, so turn it on in the info panel to see the name as well. Result: The column name staysordered_at, and the logical name turns italic (1). - Click the icon at the end of the cell. Result: A menu opens, and in its table (2) Order and Datetime are checked in the Mismatch column, because the dictionary's abbreviations (
ord,dtm) differ fromorderedandatin the current name. - To change the name to the standard, click the Rename to ord_dtm item (3). If you are only documenting, do not click it. Result: The column is renamed, and because the column exists in the database, the rename becomes a change waiting to be applied. The domain goes only as far as a suggestion, because agreeing to change the name is not agreeing to change the type. To match the type, choose it yourself in the domain cell.
Fixing from the menu
The Review needed menu shows only what applies to the cell.
| Item | When it appears | What it does |
|---|---|---|
| The Register word item | When there are characters not in the dictionary. One line per such part | Enter the abbreviation and domain on the spot to add the word to the connection's dictionary, and rebuild the physical name of the clicked column. Other columns keep their names |
| The Standardize words item | When a synonym or forbidden word remains in the logical name | Changes the logical name to the standard words. The physical name is not changed |
| The Rename to ord_dtm item | When the physical name the dictionary would build differs from the current name | Renames the column as the dictionary says |
| The Add antonym column 'End' item | When you used a word that has an antonym, and the paired column does not exist yet | Adds the paired column right below (for example, End Datetime next to Start Datetime) |
| The Details item | When words were read from the dictionary | Shows the details of the words read (abbreviation, domain, related words) |
- Type
Delivery Datetimeinto the new table. Result: Delivery is not in the dictionary, so it gets a wavy underline, and onlydtmremains in the physical name. - Click the icon at the end of the cell (1) and click the Register word item (2). Result: The Add word dialog opens.
- Enter the abbreviation
DLVin the dialog and click the Add button. Result: The physical name changes todlv_dtm. The registered word is saved as an edit for the connection only, and the original dictionary is unchanged. To add it to the original as well, edit the dictionary in the manager, or create a new asset with the clone in the data dictionary panel (Assigning to a connection).
Logical names written before the dictionary was assigned, such as those of reverse-engineered tables, are not standardized automatically. Such cells show a dotted underline; fix them with the Standardize words item.
Table names work the same way
The logical name field in the table header is read with the same dictionary. For a new table, typing a logical name changes the name right away; for a table that exists in the database, an icon to rebuild the name appears next to the logical name when the name differs from the dictionary. Clicking the icon renames the table. A table's logical name does not change the table comment in the database.
What's next
- Apply changes to the database: check and run the DDL for the new table and renamed columns.
- Assigning to a connection: edit the assigned dictionary for the connection only, and clone the edited state as a new asset.
- Publishing, versions and backup: publish account assets so your team uses the same standard.
- Document an existing database as an ERD: add logical names and share the result as a document.