...
4) Column definitions stay the same in both systems...
In the interface between the systems, we convert the EAS char 10 field into a single unique "lookup character" and use that character in the interface.
In this solution, the interface would convert "100 1/2 Main St" to "100 â Main St" where the "â" character is
- arbitrary
- generated by the interface
- will not collide with the existing ascii characters (0-127) in this field thereby supporting uniqueness properly
AVS would store the single character and displays it in the UI in the usual way.
The AVS databse will have to be able to support the insertion of unicode characters (encoding of UTF-8 or equivalent).
The downside of this solution is that for the "â" (for example) to be meaningful, the user must take an extra step look up the address to see what the "â" really means.
We can easily provide that service, but the non-meaningful character is a violation of a number of UI design principals.
That said, the existence of addresses with legitimate street number suffixes is relatively rare.
Therefore, I would anticipate that operational issues here will be a lack of familiarity of this kind of address and how to look it up, etc.
On the other hand, I think any char 1 solution here is going to have this problem.
This is because street number suffix could be "1" or "A" or "1A" or whatever.
5) change the column width in AVS but do not change the forms and reports
This is not viable. For details read on. Expanding AVS's STREET_NUMBER_SFX field to 10 characters has expected results: in some cases Oracle Forms truncates it to 1 character ("H" or "1", for "HALF" or "1/2"), in some cases, the form just fails to handle/process the address correctly, in some cases the form displays a popup that a text field is not wide enough. As mentioned before, each of the 100s of form/reports/web applications that uses addresses would need to be modified to handle the expanded field.
6) add a new char 10 column to AVS to hold the full number suffix
(this is a naive attempt since I do not know DBI business)
Add trigger code to keep the existing number suffix column in sync with the new number suffix column.
When there is an insert or update on new number suffix column
if there is one char, copy it into the existing number suffix column.
if there is more than one char, copy a special char into the existing number suffix column.
When there is an insert or update on existing number suffix column take the character and put it into the new number suffix column.
For this to work there must be a way for the user to disambiguate apparently identical columns.
...
longitude
EAS Field: address_base.geometry.longitude (double)
XML Field: base_address/longitude
FGDC Field: address longitude (double)
AVS Field:
Example:
Comment:
...
EAS Field: address_x_parcel.create_tms (datetime)
XML Field: address_parcel_link/create_tms
FGDC Field: na
AVS Field:
Example:
Comment:
TODO: determine mapping
...
address_x_parcel retire_tms
EAS Field: address_x_parcel.retire_tms (datetime)
XML Field: address_parcel_link/retire_tms
FGDC Field: na
AVS Field: ???
Example:
Comment:
TODO: determine mapping
...
address_x_parcel retire_tms
EAS Field: address_x_parcel.retire_tms (datetime)
XML Field: address_parcel_link/retire_tms
FGDC Field: na
AVS Field: ???
Example:
Comment:
TODO: determine mapping
...
structure_number character varying(3)
EAS Field:
XML Field:
FGDC Field: na
AVS Field: structure_number character varying(3)
Example:
Comment:
TODO: determine mapping
...
address_kind
EAS Field:
XML Field:
FGDC Field: na
AVS Field: address_kind character varying(11)
Example:
Comment:
TODO: determine mapping
...
address_type
EAS Field:
XML Field:
FGDC Field: na
AVS Field: address_type character varying(11)
Example:
Comment:
TODO: determine mapping
...
avs_street_status
EAS Field:
XML Field:
FGDC Field: na
AVS Field: avs_street_status character varying(10)
Example:
Comment:
TODO: determine mapping
...