Database Model

Each box in this diagram represents a separate table (entity) within the XAMPLE database. A line between two tables signifies that a relationship exists between those tables. Each relationship is of the ONE-to-MANY variety; therefore the arrow at one end of the line indicates which of the two is the MANY entity, as in:

A ONE-to-MANY relationship is sometimes referred to as a PARENT-CHILD relationship. It means that an entry can exist on the ONE without any associated entries on the MANY, but an entry on the MANY cannot exist without an associated entry on the ONE (unless the relationship is optional, in which case the MANY is not associated with any entry on the ONE).

Below is a brief description of every table in the XAMPLE database:-

X_PERSON contains PERSON details. Each PERSON has a primary and a candidate key.

X_PERS_ADDR contains PERSON ADDRESS details. As a person may change his/her address over a period of time multiple entries are permitted, each having a unique sequence number with its own start and end dates.

X_PERS_ADDR_LN contains PERSON ADDRESS LINES. Each address line is held on a separate database occurrence, with blank lines not being written to the database.

X_PERS_TYPE contains various PERSON TYPES, a way of splitting the PERSON entries into different groups. This is linked directly to X_PERSON.

X_OPTION contains various entries which may be linked to X_PERSON via X_PERS_OPT_XREF and X_OPTION_VALUE.

X_PERS_OPT_XREF can contain zero or one entries for each combination of X_PERSON and X_OPTION.

X_OPTION_VALUE can contain zero, one or more entries for each combination of X_PERSON and X_OPTION. The primary key contains a sequence number which allows multiple entries, and each entry can have its own start and end dates.

X_TREE_TYPE contains several different types of tree structure. Each TYPE has its own collection of LEVELS and NODES.

X_TREE_LEVEL contains the list of LEVELS that exists within a particular tree TYPE.

X_TREE_NODE contains the list of NODES that exist within a particular tree LEVEL.

X_SCREEN contains a list of various entries.

X_SCREEN_ITEM contains a list of items for each occurrence of X_SCREEN.

X_SCREEN_ITEM_ACC contains an entry where a PERSON's access to various ITEMs within a SCREEN has been turned off. If no ITEMs for a SCREEN have been turned off (the default condition) then no occurrence exists for that combination of X_PERSON and X_SCREEN.

X_CONTROL contains data where each item is held on a separate occurrence rather than as a separate field within a single occurrence. FIELD_ID is the primary key, and FIELD_VALUE is the value for that field.

X_HELP contains free-standing help text.

http://www.tonymarston.net