Concepts

Data dictionary

A data dictionary is the list of naming and type rules your organization has agreed on. NeoSQL brings this list into column editing in the ERD, so that typing a column's logical name fills in its physical name and type according to the standard. This page explains what words and domains each decide, where a dictionary is assigned, and what happens to columns when a dictionary changes.

Words unify names, domains unify types

Data modeling usually manages naming and type rules in three parts.

ConceptMeaningExample
Standard wordThe smallest unit of a name. Pairs an official term with its abbreviationOrder = ORD, Datetime = DTM
Standard termA column name actually used, built by joining standard wordsOrder Datetime = ORD_DTM
DomainA named type rule shared by values of the same kindAmount = DECIMAL(15,2), Flag = CHAR(1)

When people write the same meaning differently (customer number, cust_no, customer_id), the same value ends up with different names and types in each system, which costs you when integrating, querying and migrating. Standard words unify names; domains unify the types those names carry.

NeoSQL keeps these rules in two kinds of assets. They are assigned separately, so you can use just one.

AssetWhat it holdsWhat it does
Domain packA list of domains. Each domain bundles a type, length, scale, nullability, default value and auto incrementAssigning a domain to a column writes those values into the column
DictionaryStandard words and abbreviations, related words such as synonyms, and the domain linked to each wordReads a logical name to build the physical name, and assigns or suggests the domain of the words it read

Standard terms are not managed separately. The dictionary either builds them by splitting the logical name into words, or uses them only when they exactly match a registered entry (see "Combination and Exact" below).

One logical name sets both name and type

In NeoSQL you can link a domain to a word in the dictionary. If "Datetime" is linked to a date-time domain, typing "Order Datetime" as a logical name sets the physical name and the type at the same time. You do not have to apply the naming standard and the type standard in two separate passes.

Logical namePhysical name builtLinked domain
Order Datetimeord_dtmDatetime
Customer Numbercust_noNumber
Use Flaguse_ynFlag
  • The dictionary splits the name you type in the Logical Name cell (1) into words and joins their abbreviations with _ to build the physical name (2, the Column cell). Letter case follows the identifier rules of the connection's DBMS.
  • The domain linked to the words read goes into the Domain cell (3), and that domain's type (4, the Data Type cell), length, nullability and other values are written into the column.
  • Domain candidates are looked up from the last word backwards, because the last word usually decides what a column is ("Order Datetime" is a "Datetime"). If the last word has no domain, the lookup moves to the word before it. With exactly one candidate the domain is assigned right away; with two or more it is only suggested so you can choose.
  • Characters not in the dictionary are left out of the physical name, and the logical name cell marks them as not in the dictionary. Letters and digits are no exception.
  • The physical name is decided once, when it is built. If a word's abbreviation changes later, column names already built do not change by themselves.

Combination and Exact

A dictionary reads logical names in one of two ways, chosen when the dictionary is created. It cannot be changed afterwards.

CombinationExact
How it readsSplits the name into registered words, longest first, and joins their abbreviationsUses an abbreviation only when the whole logical name exactly equals one registered entry. Never combines parts
ExampleOrder Datetime = Order (ORD) + Datetime (DTM) → ord_dtm"Customer Order" must be registered to become cust_ord. Having "Customer" and "Order" separately is not enough
Domain candidatesLooked up from the last word backwards, so there can be severalOnly the domain of the matched entry
Unread charactersOnly the parts it read go into the physical name; the unread characters are markedNo physical name is built; the whole logical name is marked as not in the dictionary
Input helpNo suggestion list. While several words are being joined, the boundary of the word being typed cannot be knownSuggests registered terms as you type; picking one sets the physical name and domain together

If you work from a word-level standard word list, Combination fits. If only already-approved terms may be used, Exact fits. A connection takes only one dictionary, so if you need both, create separate dictionaries and assign one to each connection.

Assignment is per connection

  • Dictionaries and domain packs are assigned to a connection, not to a project. This lets one project hold a production database and a newly designed database that follow different standards.
  • Each connection has one slot for a domain pack and one for a dictionary. An empty slot means it is not used. If the dictionary specifies a domain pack, the domain slot is locked to that pack.
  • You assign them in the Data Dictionary tab of the connection settings, opened with theExplorer context menuData Dictionary... menu. The tab appears only for a saved connection that you have permission to edit.
  • You can also adjust an assigned pack for this connection only (Project railData Dictionary). Those changes apply to that connection only and leave the original asset untouched. To turn the adjusted state into a new asset, use the Clone current state button in the same panel.

Connections stay at the point you assigned

A connection sees an asset as it was when you assigned it. Connections do not follow changes on their own so that a standard someone else changed, or an entry you just deleted, cannot quietly change your columns. What a connection is fixed to depends on where the asset is stored.

Server assets (official packs, account assets)Device assets
Stored inThe NeoSQL server. Official packs are provided by NeoSQL; account assets belong to your account and are shared across devices and with teammatesThis device. The connection settings show the This device badge
What is assignedOne published version. An asset with no published version cannot be assignedA copy taken when you assign it. There is no publishing and no version
After the asset changesPublishing a new version leaves the connection on its version; only a badge such as New version v2 appearsThe connection keeps seeing its copy. If the domain pack or dictionary changed, the Data Dictionary panel shows Source changed; click it to pull in the current content. Changes made for this connection are kept
ExceptionPublishing with the Overwrite current version button applies the change right away to connections on that versionNone

Device assets are copied too so that deleting an entry from an asset does not also remove it from every connection that uses the asset.

What local projects can assign

Local projectAccount project
Assets you can assignOfficial packs, assets on this deviceOfficial packs, public assets, account assets you own or co-edit
Account assetsCannot be assigned, even when signed inCan be assigned
Device assetsCan be assignedCannot be assigned
Where assignments and connection-only changes are storedThis deviceYour account

A local project is not linked to an account. If it used an account asset, it could no longer read the original after you sign out or open the project on another device, so account assets are not listed even while you are signed in. Device assets, in turn, exist only on this device, so they cannot be assigned in account projects.

Device assets come from these places:

  • On the desktop without signing in, assets you create in the App menuData Dictionary Management screen, or public assets you download there, are stored on this device.
  • On the desktop while signed in, an asset you create in the same screen becomes a device asset when you check the Save on this device box. Otherwise it is an account asset.
  • Copies you make in a local project by assigning an official pack and pressing the Clone current state button in the Data Dictionary panel are also created on this device regardless of whether you are signed in.

Columns already in the database do not change on their own

What changes when you type a logical name depends on whether the column already exists in the database.

ColumnWhen you type a logical name
A column being designed (in a new table, newly added, or without a name)The physical name is built and filled in, and the domain is assigned if there is exactly one candidate
A column that already exists in the databaseNeither the name nor the type changes. If the name differs from the dictionary, the logical name cell only marks it, and the domain is only suggested

Adding logical names to an existing database is usually for documentation. If each one changed names or types, changes waiting to be applied to the database would pile up, and a shorter length could truncate data. You decide whether to rename a column to the dictionary name by clicking the mark in the logical name cell. Renaming it does not change its type.

Domain values are copied into the column

  • Assigning a domain copies its values into the column. Nothing is kept as a reference, so the ERD and DDL keep working even when the asset cannot be opened.
  • If you edit a value the domain decides directly in the column and it no longer matches the domain, the column's domain assignment is removed. There are no per-column exceptions.
  • When you edit a domain on a connection and save, the Domain change impact dialog finds the columns using that domain and lets you choose which ones to update.
  • If you remove the connection's domain pack or switch to another pack, the columns' domain assignments are removed as well, but their types and lengths stay as they are.
  • Logical names are not written to the tables created in the database. See ERDs and the real database.

Things to keep in mind

  • The Match Type setting (Combination or Exact) is set when the dictionary is created and cannot be changed.
  • Assets created in the management screen while signed in are account assets unless you check the Save on this device box, so they do not appear in a local project's list.
  • Device assets exist only on this device. To move one to another device, create a backup file with the Export button on the asset screen and import it there. The imported asset becomes an account asset or a device asset depending on whether that device is signed in.
  • New versions of official packs and account assets are not applied automatically. To use a new version, pick the asset again in the connection settings and choose the version.