I’m trying to develop a database to track trucks. I receive information on trucks comes from unrelated sources and has format issues and naming issues. I’ve solved the import differences. Managing and finding an elegant solution to various naming methods for cities has me stumped.
The problem I have is the information on trucks coming into a city may have the city as Denver or Denver, CO or some other variation. I want to use a standard city, Denver, CO, to represent all of the variations.
I have set up a table with the standard cities and another for the import from the varied truck scheduling sources. What I want to do is to look up the standard city for the varied inputs and at the same time capture any new variations in the input city just so I can relate them to a standard city. I don’t mind having to do some manual editing as I believe over time that will be minimal.
My original thought was three tables a schedule input table, a join table and a standard city table. Since I’m here asking for help I’ve yet to make this work correctly. Any suggestions?