Deleted Dictionary Entries

When you wish to delete a dictionary entry, If the dictionary is a description-only table, the software will physically delete the entry.  If the dictionary is a keyed-table, the software will flag the entry as deleted.  The reason that description-only table items may be physically deleted and the keyed-table items must be flagged as deleted is technical in nature, but it needs to be discussed anyway.

Dictionaries do not live in a vacuum.  They exist in order that higher-level dictionaries or even master-level tables may be built with accurate data.  For example, the Sex Dictionary Table and the Specialty Dictionary Table are used by the Staff Dictionary Table.  The Sex table is a description-only table containing "Male", "Female", and "Unknown".  The Sex field in the Staff table contains the value "Male", "Female", "Unknown", or null (if no Sex was entered/selected).  If we later go back and delete the "Unknown" entry from the Sex table, the Staff table will still contain the values in the Sex field.

However, the Specialty table is a keyed-table.  It contains Key, Lookup, Name, Code, and other fields.  The Specialty field in the Staff table contains the key to the Specialty table, not the Lookup or Name values.  If an item were to be physically deleted from the Specialty table, any Staff item where that Specialty key was used would now be "pointing to" a Specialty item that no longer exists.  Referential integrity is the term for the process used to ensure that this sort of problem does not occur.  Lack of referential integrity would create all sorts of havoc in any system.  Therefore, to prevent this problem, when we want to delete an item from a keyed table, we simply flag it as deleted.  If an item is flagged as deleted, it cannot be selected from a lookup field but it is still available for reporting purposes.