HFT Release Notes

Release 0.1, August 7, 2004

GENERAL NOTES AND QUESTIONS:

Contents: Data Model (hft.pdf), files to support an initial mock-up of the application interfaces, and this document of application development planning notes. Per your spec, the next step would be to have a meeting to go over HFT with one or more users. Or if the mock-up looks OK, we could proceed directly into the conversion and adjust it as we go forward. Please let us know how you want to proceed.
User Interface Mock-up: The supplied mock-up is not fully functional but describes the direction we will take in implementing the user interface. The query capability makes use of EBMUD's existing query templates and may change in appearance as we proceed with development. To simplify the menu structure, we have added 3 links at the top. These links are toggles to turn on/off the main query menu section, the reports section, and the Administrator's section. The Control Table Maintenance link in the Admin section shows the table list, but is not yet functional beyond that.
Object Orientation Conversion: We propose to convert the core HFT tables fully, including:
1. Renaming the tables with a _DATA suffix
2. Creating content-rich views with the original table names
3. Creating EBMUD-standard package interfaces to the tables
Regarding the reference, or look-up tables, we propose to leave the current table names as they are and provide an EBMUD-standard admin package interface to the data. Nothing would be gained by suffixing the tables with _DATA and providing views by the same name as the original tables that would otherwise look just like the original tables.
Data Model: The delivered Data Model shows the proposed Object Orientation Conversion. Please verify or answer the following statements we have derived from the supplied data:
1. Most hydrants have 2 outlets, some have 1 or 3. None have more than 3.
2. A hydrant test can cover more than one hydrant. Each hydrant will be tested and have only one flow recorded in the hydnt_flow table for that test.
3. Only about 2/3 of the hydrant tests have an associated pumping plant recorded for them, so identifying a pumping plant is optional.
4. Only about 15% of the hydrant tests have an associated reservoir recorded for them, so identifying a reservoir is optional.
5. No hydrant has a city_cd that is null, even though the city_cd allows nulls. Is the city_cd ever unknown during the life cycle of a hydrant, or should this be made a non-null column during conversion?
6. Most, but not all, hydrant tests will have one or more adjacent pressures recorded. Only one very early test in the data supplied has ever had more than one adjacent pressure recorded. Do multiples need to be accommodated in the user interface, or are multiples no longer allowed?
7. About half of the hydrant tests are not associated with a hydrant agency so hydrant agency is optional.
8. Most entries in hydnt_serv do not require an entry in hydnt_serv_agn.
9. The hydnt_fire_stn table as supplied contains no data but is referenced in several forms. Only 5 of the entries in the hydnt_test table are not null in this column. Is this column obsolete? If the column/table are not obsolete, we will need data for the table.
Specific Task 1: The HYDNT table we received has already undergone this street name transformation, so this part of the task will be skipped. Questions:
1. The table contains columns HYDNT_PFIX_CD and HYDNT_ST_PFIX_CD. These appear to be the same, but there are cases where one column is null and the other not-null. HYDNT_ST_PFIX_CD appears to be the correct translation of the HYDNT_PRIM_ST_NM data. Is HYDNT_PFIX_CD redundant? If so, should it be dropped from the table?
2. The combination of HYDNT_BLDG_NU, HYDNT_FRACT_NU, HYDNT_ST_PFIX_CD, HYDNT_ST_NM, HYDNT_ST_SFIX_CD appear to define the nearest address to the hydrant. Is this correct?
3. The combination of HYDNT_XREF_ST_PFIX_CD, HYDNT_XREF_ST_NM, and HYDNT_XREF_ST_SFIX_CD appear to define the nearest cross street to the hydrant. Is this correct?
For the HYDNT_SERV_DATA table, columns HYDNT_LOCK_CAP_INSTALL_FG and HYDNT_LOCK_CAP_INSTALL_DT have been added to the data model. We also added a trigger to prevent null comments for status codes OUT and OTH. This trigger will be included in a conversion script to be delivered when the new data design is approved.
Specific Task 2: An initial data model is included in this delivery as a PDF file. This can also be delivered as an ERWin or ER/Studio file if desired.
Plans for Tasks 3 to 11:
- 3: Access to the various update procedures in the packages will be controlled by the user's active Roles, per your specification.
- 4: We will use the PL/SQL Object Generator supplied by EBMUD to develop Admin interfaces to the specified tables. We will edit the generated code to correct a few minor errors created by the generator and to "beautify" the resulting interfaces.
- 5: We will extract the business rules from the triggers and procedural code in the HFT Oracle Forms and document each one as it is implemented in the application. Business rules will be enforced in the database packages and the user interfaces will be created to work easily with the business rules using poplists and javascript for client side validations where possible.
- 6: Data maintenance for all tables, from users or admins, will be implemented through database packages in the same manner as ETS.
- 7: Web interfaces will be developed using ColdFusion for user interactions with the data. We will make full use of poplists for picking codes and client side javascript for other validations such as required fields. The Web interfaces will cover the specified tables.
- 8: We will provide a query interface as specified using the common two or three frame query model (most of the work we have done has two panes rather than three).
- 9: The specified reports will be redeveloped and included in the application menus.
- 10: We will provide User and Admin Guides similar to those developed for ETS. These guides will grow along with the application as it is developed.
- 11: One of the first things we will need to do is develop the conversion script to convert the supplied schema to the new structures. We will develop and test this conversion script and supply it with one of the early deliverables.
Other Questions or Issues:
- Query Capabilities: Item 8 requests query capabilities by specific fields, then says the is already a query page and not to "re-invent" one. However, the existing query page does not allow for query by the specified fields. Is the existing query page OK for this project, or should we rewrite the query to be based on the specified fields? The initial prototype uses the existing query approach but can be easily modified.