XenDirect is comprised of almost 300 data tables, which are related to and depend on each other. Understanding the relationship between data tables and how the data in one table can affect another will provide you with the skills to manage your data better.
Tables store records of a similar type. Some tables have primary importance in the entire system such as the Clients table or the Courses table. Throughout the knowledgebase, you will see these tables referred to as a Parent or primary table. Other tables may be of secondary importance within the system, such as Case Notes or Fees. These tables, known as Child or Secondary tables, are a sub-level to a Parent table. In other words, a record in a Parent table must exist in order for the Child table to contain any related records. The third category of tables is the Selects. These data tables are often used to help classify and categorize Parent and Child tables and are most often seen as the items in a select list.
XenDirect tables are related to another table in one of two ways. The majority of all table relationships in XenDirect fall under the first category.
-
One-to-Many: Two tables related to each other but for every record in the Parent table, the Child table can contain multiple records.
- Example: Client table to Assignment table
-
One-to-One: Two tables related to each other but can never contain more than one match between them.
- Example: Client table to Client Intake/Exit table
Tables are related to each other using Primary Keys. In most cases, these Primary Keys are hidden fields that are usually integers and not accessible to the user. Primary Keys cannot be duplicated within the same Parent table. Even though the Primary Keys are hidden in a Child table, these Primary Keys link a Parent table and a Child table.
The other feature of related tables is the prevention of duplicate data across tables. For example, the Client ID is located in the Client table. There is no need to store the Client ID in any other table because the Primary Keys that link related tables make that unnecessary. If you change a Client ID in the Clients table, the ID will automatically be updated in all related tables and records because of the Primary Key link.
However, if you attempt to delete a record in a Parent Table that has related records in a Child Table, the delete process will terminate without deleting any records. The multi-table delete process (called cascade delete) is disabled in XenDirect to prevent the user from deleting records in multiple tables by accident.
The main XenDirect tables are:
Branches
Branches are a feature of XenDirect that is similar to a unit or unit of the same organization in the same way that banks have branches. The Branch table is the primary location where system-wide information, settings, preferences, and defaults are recorded. For many organizations, their XenDirect setup only includes one Branch. In a multi-Branch setup, each Branch has its own profile and settings.
Tables Common to All Branches
In a multiple Branch setup, some tables are not Branch unique. The records in these tables are shared across all Branches.
The following is a list of the tables shared across Branches. Not all tables listed are available in all XenDirect versions. The title bar that displays the name of the data table will display "Branch Shared" on a table that is shared across branches.
-
Basic
- Companies
- Company Log
- Company Type
- Country
- Course Activity Type
- Course Format
- Degree Type
- Ethnicity
- IP Allowed/Blocked
- Language
- Name Prefix
- Organization Profile
- Payment Type
- Postal Codes
- Program Group
- Permission Group
- Room Type
- State
- System User
-
Plus (all above and the following)
- Employment Cycle
- Job Category
- Test Type
-
Enterprise (all above and the following)
- Business Codes
- Company Donation (this table no longer used in Release 3)
- County
Select Lists
Select Lists are the tables of data used to manage the data in drop-down lists throughout XenDirect.
- In most cases, these are user-defined lists where you control the content.
- In a few cases, you cannot add to the list, but you can manage some of the values in the list.
- Some lists are Branch unique, and some are Branch shared.
- If a change is made to a value in a Select List, the change affects all past and future records related to the list. For example, there is a Marketing Source record called "Newspaper". It has been used 232 times in other records. A user changes the name to "Periodical". All 232 records are now linked to "Periodical" and "Newspaper" no longer exist. This is true for both Branch unique and Branch shared lists.
Rick Stern
Comments