Main Index  PREV  NEXT
menu_banner (2K)

Database Model

ACCOUNT table CONTROL table FAVOURITES table HELP_TEXT table INITIAL_VALUE_ROLE table INITIAL_VALUE_USER table LANGUAGE table MENU table MOTD table NAVIGATION_BUTTON table PATTERN table ROLE table ROLE_TASK table ROLE_TASK_FIELD table SUBSYSTEM table TASK table TASK_FIELD table TODO table USER table TASK_IP_ADDRESS table USER_IP_ADDRESS table database-model (5K)

Each box in this diagram represents a separate table (entity) within the menu 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:

one-to-many (1K)

Here is an explanation of the tables in my database:

ACCOUNT This contains an entry for each account, and is only used where database tables hold data for different accounts and where the data for each account is only visible to users within that account. Please refer to Implementing Virtual Private Databases for details.
CONTROL This holds control data, equivalent to a file of configuration options.
FAVOURITES This holds each user's favourite tasks.
HELP-TEXT This holds the 'help' text for the task in the default language. When the Export Subsystem task is run it will copy the text to a series of files, one per task, in the help/<default_language>/ subdirectory. Translations of this text can then be created in different language subdirectories.

When the HELP hyperlink is pressed the help text for that task will be retrieved from the language subdirectory, not the database table, which is appropriate to the user's language.
INITIAL-VALUE-ROLE This is used to define the initial value for a field when a specified TASK is run by a USER within a specified ROLE. This table has two uses:
  • If the TASK is responsible for creating new records (i.e. uses the insertRecord() method) it will be used to supply initial values. These may be overridden before the record is actually inserted.
  • If the TASK's pattern is LIST then this will be used to supply initial selection criteria before data is retrieved from the database.
NOTE: entries on the INITIAL-VALUE-USER table will take precedence over entries found on this table.
INITIAL-VALUE-USER This is used to define the initial value for a field when a specified TASK is run by a specific USER. This table has two uses:
  • If the TASK is responsible for creating new records (i.e. uses the insertRecord() method) it will be used to supply initial values. These may be overridden before the record is actually inserted.
  • If the TASK's pattern is LIST then this will be used to supply initial selection criteria before data is retrieved from the database.
NOTE: entries on this table will take precedence over entries found on the INITIAL-VALUE-ROLE table.
LANGUAGE Of all the possible languages which exist, this identifies those which are actually supported by the current application. Please refer to Appendix O: Internationalisation for details.
MENU Entries on the TASK table which are of type 'MENU' require to have their contents maintained on the MENU table. When a user selects a menu the contents of the MENU table are retrieved and displayed in the menu bar area.

By using the entries on the ROLE-TASK table any MENU option which is not accessible to the user can be filtered out, thus restricting the display to only those options which the user is actually allowed to access.

TASK_ID_SNR identifies the menu page while TASK_ID_JNR identifies an option on that page.
MOTD This stores 'Message Of The Day' entries.
NAVIGATION BUTTON There are some child tasks which cannot be made available until a relevant parent task is active, and these child tasks are displayed in the parent task's navigation bar. This is because the child tasks require context to be passed down to them, and context is supplied by the parent task. For example, you must select one or more entries in a LIST form before you can pass control to an UPDATE, ENQUIRE or a DELETE form.

By using the entries on the ROLE-TASK table any NAVIGATION BUTTON option which is not accessible to the user can be filtered out, thus restricting the display to only those options which the user is actually allowed to access.

TASK_ID_SNR identifies the parent component while TASK_ID_JNR identifies a child component.
PATTERN (DIALOG_TYPE) Each task conforms to one of the patterns in Transaction Patterns for Web Applications, and I have found it very useful to have this as an identifier on each TASK record. For example, when selecting entries for the ROLE-TASK (permissions) table I can very quickly isolate all the tasks of any particular pattern/template.
ROLE This is a method of dividing users into groups or classes so that members of each role/group/class can share a common set of permissions.
ROLE-TASK This identifies which TASKS can be accessed by which ROLES and is sometimes known as the access profile, security profile, permissions list or access control list. This table only contains entries where a particular combination of ROLE and TASK is valid.
ROLE-TASK-FIELD This is used in conjunction with TASK-FIELD to change a ROLE's access to a field within a TASK. The access options are:
  • FULL - read and write access (the default).
  • NOEDIT - read only access.
  • NODISPLAY - no access, the field will be removed from the screen.
SUBSYSTEM Each application or system can often be broken down into discrete parts (or subsystems) which can be regarded as separate collections of components. For example, 'Menu and Security' is separate from 'Workflow' which is separate from 'Product' which is separate from 'Customer'. The files for each subsystem may exist in a separate directory. It is quite often that a user will have responsibility in only one of these areas, therefore it is useful to have this as part of the selection criteria when maintaining access permissions.
TASK This contains an entry for each task within the system. These are sometimes referred to as 'transactions', 'modules' or 'functions'. Two types of task are supported:
  1. MENUS - these are used to group tasks into menu 'pages' where each 'page' will contain a number of menu items, options or selections.
  2. PROCS (procedures or processes) - these use externally-defined scripts to perform some action. The record will therefore contain the location and name of the corresponding script.
TASK-FIELD This is used to define the fields within a TASK that can manipulated further via entries on the ROLE-TASK-FIELD, INITIAL-VALUE-USER and INITIAL-VALUE-ROLE tables.
TASK-IP-ADDRESS This identifies all the IP addresses which are valid for this task. If there are no IP address entries for a task then there are no restrictions on that task, and the task may be accessed from any device. If there are any entries then access is only permitted from a device with an address which is in that list of entries.
TODO This holds the "to do" list for individual users.
USER This contains an entry for each person who is allowed to access the system. It is used by the LOGON screen to verify the userid and password.
USER-IP-ADDRESS This identifies all the IP addresses which are valid for this user. If there are no IP address entries for a user then there are no restrictions on that user, and the user may access the system from any device. If there are any entries then access is only permitted from a device with an address which is in that list of entries.
copyright (1K)
http://www.tonymarston.net
http://www.radicore.org

counter