...
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.
...