| 1.1 Portfolio
11 Reference Portfolio | |
|---|
| 1.1.11 Reference Portfolio: | | Private and confidential business data is encoded and encrypted using many different methods to keep it safe and secure. Each security improvement may be a small step, but continual improvements and excessive security mean that stored business data cannot be stolen because it is meaningless. Where others encrypt just a password, we chose to encrypt every field so nothing can be read or understood. | | The threat of a data breach is eliminated when the stored business data is unreadable, meaningless and just a set of numbers. |
| Cost: | | Development and maintenance costs are reduced - using CSV files is faster and cheaper to implement. Storage space is reduced as long descriptions are held once and each instance is just a number. Process time is increased as the entire file must be read into an array before any description can be shown. Reference data can be cached and reused according to usage algorithms. SQL reference selects are reduced for lists and forms. |
| Reference Data: | | Reference data has unique characteristics that make it very different to business data. For example: a list of country names is reference data. Reference data is unlikely to be private and confidential - it is likely to be published data in the public domain. Reference data does not get updated very often - the occasional new record is all that is needed. Reference data audit trail history does not serve a purpose. Reference data does not have an expiry date when it must be destroyed. Reference data tends to be one or two descriptive fields indexed by a unique (auto-increment) key. Reference data is shared by all offices - records are not owned by a single office. | | Reference data is different to web options (WOP) in that it may have a large number of records. Web options are generally restricted to less than 200 options in any drop down list. |
| Reference Spread Sheet: | | Reference data is managed exclusively with the common spread sheet function and no other function - a massive simplification. The spread sheet includes a "Clone" option so the occasional new record can be inserted by cloning any existing record and editing the result. The spread sheet includes an "Edit" option so the occasional spelling correction can be applied. If needed and when approved, the spread sheet has a "Delete" option that will physically delete a record. | | No other function is needed to manage reference data. Rather than a pair of tables to hold primary and historical data, historical data does not need to be stored. Web Logical Record (WLR) entry is not needed when a 4 character record name is involved. |
| Reference Examples: | | VRTN Town Name List (28kb). | | VRCO County Name List (2kb). | | VRCY Country Name List. | | VRCU Currency Code and Name List. | | VRAP Airport Code and Name List. | | VRFC Airline Flight Code Name List. | | VRSI Industry Sector Number and Name List (5kb). | | VREM Email and Web Domain Name List. | | VRQU Qualification, Course and Skill Name List. | | VROC Occupation and Job Title Name List (14kb). |
| Reference Table Example: | | 01 Primary Key Int auto-increment. | | 02 Name V32. | | 03 Code V8. |
| Security: | | Reference data provides an increased level of security. Stored business data fields hold an encoded number that is meaningless to any criminal. Only application services knows how to decode the number into a primary key that selects the description that is shown on a business form. For example: each CRM record holds a town number rather than a town name - this makes it much harder for a criminal to sell the data. |
| NoSQL: | | Reference data is not stored in the database, it is held as a NoSQL file in a private folder. Any spread sheet select on a table beginning with "V" is read from a NoSQL file. Any edit or clone will update the NoSQL file and write it back to the private folder. The NoSQL is treated as a long string (CSV) using semi-colons (vertical bar) rather than commas. | | File and folder encryption add yet another layer of security so any data that is copied is meaningless and valueless. The unique key value is of no consequence, except that it must be unique and never changed. | | Spreadsheet paging is OK, order by any one column at a time is a known logic limitation and filter by any one column. Spreadsheet cookies work as normal to remember any configuration changes. |
| Recovery: | | Before a change is made to a file, a copy of the file written with a date suffix. In the event that a change is defective, an earlier edition of the data may be restored by date. Any number of changes may be made on the same date, but only one copy of a file is backed up by date. |
| Replication: | | Periodically, a copy of reference files are replicated to many other data centers. Periodically, old recovery files that are more than a certain number of days old are physically destroyed as having no purpose. |
| NoSQL: | | NoSQL is a method of storing data in files rather than a database. This has the benefit of avoiding database administration, however such data gives-up the benefits of a database such as audit trails, history and recovery. | | Stored File formal is semi-colon separated values with a prefix and suffix field on each line to enclose the data. The first cell must be a 6 digit unique key that cannot be changed. | | Internal array is a dataset of rows and columns that is filtered and sorted like a normal SQL dataset. | | Description as c02 is stored in upper case, shown capitalized and filtered as if case does not matter in lower case. |
| Limitations of Edition 1: | | 1. Sort will only be applied to one column - extra logic is needed to sort by more columns. | | 2. C02 will be stored in upper case and shown capitalized. | | 3. Only one reference data spread sheet can be updated at once - opening a second reference sheet will prevent updates to either sheet. |
| Facilities: | | Insert as normal to replicate the selected row and to auto-increment a new unique key - the new description is then manually updated. | | Delete as normal to replace the row prefix with a date so the row will no longer be shown. A deleted row could be restored by editing the file. | | Update as normal AJAX with "saved" reply. A copy of the file is backed-up before a change is made - only one backup file per day. | | Paging works as normal with page size stored in a cookie. | | Mode works as normal mode stored in a cookie. | | Move columns left and right, with show and hide is stored in a local cookie. | | Filters work as normal with filter values stored in a cookie. | | Sort order works on the last column that has a sort order selected. |
| Benefits: | | Reference data is in the public domain, is not private and confidential, and does not need audit trails. | | It is quick and easy to create a reference CSV file with prefix and suffix columns - no database administration. | | Only one spread sheet 4GL will provide all required functions as normal. |
| Mobile App: | | Reference data may be managed using a mobile phone - the user interface is touch oriented. A drop down list is used to select insert and delete functions - does not need a mouse to work. Very hard to delete the wrong row - the delete key is not used. Paging is just a click of a button or vertical scroll. Field update is normal Ajax with automatic saved. |
| Reference Data: | | Public: UK Town names. UK County names. Country names. Base Currency names and codes. Timezone names and offsets. VAT code and rate. Month names. Day names. Active-Inactive flag. Yes-No flag. | | Bespoke: Standard Industry Code (SIC) names. Airline names and flight codes. Airport names and codes. | | Private: Email Domain names. Language names. Occupation (Job Title) names. | | Private: Contact Method options. Kind of Business Associate. State of BA. Kind of Task. State of Task. | | ASP: Welcome page; Role; Hours; Days; Check; Expiry; Place; ISP. | | Private: Gender; Function; Ethnic. Type of DBS. Type of Transaction. |
| Simplicity: | | Reference data design may be optimised for ease of development or ease of maintenance. A simplistic design is to store all reference data in a table with a common maintenance spread sheet - ease of development. But this implies that all reference data is the same - sorry it is not all the same and different storage methods can be beneficial. |
| Classification: | | Reference data has been analysed in detail and the following "kinds" of reference data have been identified:- | | 1. Private and confidential business reference data such as the state of a task that may be improved occasionally as the business evolves. | | 2. Public reference data such as county, country, language, timezone and currency names that are very unlikely to evolve. | | 3. TIES reference data such as library names, month names, welcome pages and security factors that may be pseudo logic - data used by functions. | | 4. Lookup reference data where the quantity of data demands a unique file store such as 1500 UK Town names or 2000 Airport names. |
| Implementation: | | 1. WOP is the ASP reference data facility to store TIES code/description reference data - WOP is only accessible by ASP office. | | 2. UOP is the business reference data facility to store private code/description reference data - UOP is accessible by Head Office HRM application - user managed. | | 3. Public and Lookup reference data may be stored as CSV files or bespoke tables - only available from ASP dashboard. |
| Code-Description: | | 1. Codes are stored in business data as an 8 digit key in a 4 byte integer (replacing a 3 character code in 4 bytes). | | 2. Where extra data is needed such as a timezone hours offset, then a bespoke table or CSV file is deployed. | | 3. Descriptions are up to 32 characters in WOP and UOP. Where a longer description such as a Standard Industry Code is needed, then a CSV file is deployed. |
| Expansion: | | Where reference data is stored in a file, it can be beneficial to store only a 3 digit key. When the list key is stored in business data, the 3 digit key is assigned a 5 random digit prefix so it looks like a normal 8 digit key. When the list key is selected from business data, its 5 digit prefix is removed. | | A benefit is that common options are stored as unique 8 digit keys. For example: "England" is a common country name, but each instance is stored with a unique 5 digit prefix with a common 3 digit country key. This will make it harder to identify where the country data is stored. |
|
|