...
EAS Field: address_base.base_address_suffix (char 10)
XML Field: base_address/base_number_suffix
FGDC Field: address number suffix (text)
AVS Field: AVS_ADDRESSES.STREET_NUMBER_SFX (VARCHAR2 1)
Example: "1/2"
Comment: MAD-122 122
TODO: resolve column width issue
Discussion
The issue here is that widening the AVS column would trigger significant changes in many parts of DBIs systems.
Moreover, much of that work would be tossed out when they adopt the new permitting system within 1-2 years.
Here are the alternatives
1) Change the AVS column to char 10.
As stated above, this will be expensive and the ROI will be low.
...
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 8 or equivalent).
The downside of this solution is that for the "â" to be meaningful, the user must take an extra step look up the address.
We can easily provide that service, but the non-meaningful character is a a violation of a number of UI design principals.
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.
...
EAS Field: addresses.address_id (int)
XML Field: unit_address/address_id
FGDC Field: na
AVS Field: na
Example:
Comment: EAS primary key for unit address
...
EAS Field: addresses.unit_num_prefix (char 5)
XML Field: base_address/unit_number_prefixna
FGDC Field: na
AVS Field:
Example:
Comment:
TODO: EAS will drop this field from database schema
...
EAS Field: addresses.unit_num (char 20)
XML Field: base_address/unit_number
FGDC Field: unit identifier (text)
AVS Field: AVS_ADDRESSES.UNIT_SFX (NUMBER 6VARCHAR2 10)
Example:
Comment:
TODO: AVS must widen fieldchange addresses.unit_num (char 20) to addresses.unit_num (char 10) (no data loss)
Discussion: We put the unit_num into the AVS unit suffix as a whole because unit_num could be any arbitrary char 10 string with or without an integer at the beginning.
...
EAS Field: addresses.unit_num_suffix (char 10)
XML Field: base_address/unit_number_suffixna
FGDC Field: na
AVS Field: AVS_ADDRESSES.UNIT_SFX (VARCHAR2 10)na
Example:
Comment:
TODO: EAS will drop this field from database scehma
...
EAS Field: addresses.unit_type_id->d_unit_type.unit_type_description (varchar 50)
XML Field: unit_address/unit_type
FGDC Field: unit type (text)
AVS Field: na
Example: suite, apartment
Comment:
TODO: EAS will add this field to the XML - do we need to map it to an AVS field?
...