Update

I’ve run into more problems with this python/flask/ords/adb app idea of mine. Turns out I’m too [developer] dumb to do some of the things that I’d originally envisioned. Now that I’ve become more familiar with python, flask, the Oracle Autonomous Database, and our Database Tools (like ORDS and Database Actions), I’m realizing that I need to rethink my approach.

First off, I’ve updated the (not trying to be pretentious here) User Journey for this project.

SIDE NOTE: The User Journey is something that I really should pay more attention to (especially considering it was one of my concentrations in my MBA program, and it was a huge initiative in my previous role at Big Blue) and you should too!

Ch-ch-ch-ch-changes

I’m leaning towards (after a recent discussion with that jeff smith) more of a drop-down style of search. I’d like to present a user with all the available cities (having taken these from my “Restaurants” table) in Wake County.

I think I can populate a list of cities to choose from with the help of REST APIs (via ORDS). But I need to do some clean-up first.

Side note

In about a minute, you’ll probably come to the realization that my approach is less than elegant. Remember, I’m not a developer. I’m kind of stumbling through this, and I’ll need to make some sacrifices along the way (we call those “trade-offs” in business-speak). Unfortunately, there are simply some techniques/skills that I do not know yet. For a visual representation of these deficits, I draw your attention to:

Back to your regularly scheduled program

On to clean-up. The cities in this table are a bit all over the place. Let’s take a look.

From Database Actions, I can navigate to a SQL Worksheet. From there I can inspect my tables (Right-click > Edit), or jump straight into the SQL. Since I know my “CITIES” column is the problem, I’ll run this:

SELECT DISTINCT CITY
FROM ADMIN.RESTAURANTS
ORDER BY 1

Some screenshots of what this looks like in Database Actions:

NOTE: The "DISTINCT" parameter drops any duplicates, "ORDER BY" returns results in ascending order.

I suspect that the program used to input health inspections allows for some level of free text.

Tangent / Story

I’ve used an application similar to this in a previous life, but it was a Computer Aided Dispatch (CAD) application. You could enter in free-text, or select from users’ previous entries. There was a lot of flexibility, but because of that, there were many variations on things like street names, cities, locations, parking lots, person names, etc. For the user, awesome, for the developer or database administrator, less so.

At this point, it is pretty clear the amount of normalization I’ll have to do to all these city names. Now I just need to figure out:

• case to use (Upper, Sentence, Lower, SaRCaSTic, etc.)
• city name variations to use

I randomly chose a city with the following SQL command:

UPDATE Restaurants SET CITY = 'Holly Springs'
WHERE UPPER(CITY) = 'HOLLY SPRING'

In this example, I searched for all instances of “Holly Spring” and replaced them with the correct “Holly Springs”. I can then repeat the process for all other cities. Again, the abridged walk-through of this is here.

I actually know this data well, since I grew up in the area. So deciding on the correct city names shouldn’t be too challenging. But familiarizing yourself with your own data might not be such a bad idea. For instance, in this table, one of the cities displays as “Research Triangle Park”. I don’t think it’s common to refer to it like this; we usually just call it “RTP”. I think little details like that really help to elevate your application.

Next steps

I’ll keep this brief. But what I think I’m going to have to do next is create a REST API based on this Restaurant table. I should have a better idea in a few days. But the idea is, that once a user selects a city, it will pass that city as a parameter to a GET request (again, courtesy of ORDS) and then return additional data. A user would then be able to further filter on that returned data, eventually ending on their target restaurant.

Okay, stay tuned…as I feel like I’m on the precipice of something exciting!

Catch me if you can

• Twitter
• YouTube
• GitHub
• Discord
• LinkedIn
• Stack Overflow
• Medium

---

Abridged walk-through