I’m not a Database Administrator, but I feel as though I may be slowly evolving into one…
In a previous post, I left myself the task of correcting the html on a proposed Python/Flask/ORDS/Autonomous Database application. My then next step was to add a search bar (which I did):
Although this was a small step forward it led to some serious questions. While having a search feature like this is good in theory, in practice it is totally unusable.
The Problem
If a user wants to search for a specific restaurant, they’d need to know a lot of information beforehand. At a minimum, the name of their target restaurant. But even that isn’t enough. Look at when I query for a single restaurant (randomly chosen – I’m not sponsored by Wendy’s btw):
Some of these restaurants come back with additional store numbers (probably a reference to either a corporate- or franchise-owned property). So a search like this simply will not do.
That’s problem number one. The second problem is that in the original datasets, while HSISID and Permit ID (read about them here) are unique in the Restaurants table. That isn’t the case with the Violations and Inspections tables. There are a TON of entries for each restaurant in these tables, and the HSISID and Permit IDs are used repeatedly for subsequent inspections. Just take a look at this example (they’re all like that though!):
A Closer look
Above, is the Violations table, but the Inspection table looks similar. From what I observer, each time a violation or inspection is recorded that same HSISID is used (Permit IDs too). But in these two tables an Entry ID is used as the unique identifier. I know the image is greyed out, but if you look closely you can see the “Object ID” in that screenshot.
But these Object IDs don’t mean anything outside of this table. And it underscores the point that my original approach to searching (i.e search bar) using a single identifier won’t work. Across the three tables these restaurants are uniquely identified in different ways.
At a minimum, these tables should be linked so that when an HSISID is searched for in the Restaurant table, it gathers all associated data from the Violations and Inspections table too. Meaning, if I search for a specific “Wendy’s” only that target restaurant comes back along with all historical inspection and inspection violation information as well.
Now that we understand the problem, I’ll attempt a solution. For my next feat, I’ll relate the three tables to one another in a logical way. Continue on dear reader…
Navigating
Navigate to your Autonomous Database, then Database Actions. Once there, look under the “Development” section, and choose “SQL”.
NOTE: I'm sure there are other ways to accomplish this, but this is what worked for me.
Once in that SQL Worksheet, look at the the left side of your browser, you’ll see the Navigator tab. Of the two drop-down lists, look at the second one. That is the “Object” selector; for my purposes I made sure that Tables was selected.
I have the three tables; two of which (Inspections and Violations) need Foreign Keys while the third table (Restaurants) will need a Primary Key.
TL;DR - The Restaurant table is like my Parent table, whereas the other two tables are like children (or dependents). Establishing these Primary and Foreign keys is a way for me to easily establish interdependence/relation among these three tables.
Establishing a Primary Key
I’ll use the Restaurant table as an example. Here is the step-by-step…
Right-click on the table (Restaurants), then select “Edit”.
If a Primary Key has already been established, then you’ll see a Column (which is actually displayed as a row in this Table Properties table) that already has a check in the “PK” column.
If there isn’t one, continue by clicking the Primary Key option (see the left-most red arrow).
Once in the Primary key option, you’ll see the following boxes with checks:
- Enabled
- Initially Immediate,
- Validate
I’m using the HSISID (found in the “Available Columns” list) as my Primary Key. Once I select that, the “Add Selected Columns” arrow will illuminate (from white to gray). Click it once to move your selection over to the right in the “Selected Columns” list.
Once your column has been moved, you can click “Apply”.
You’ll see this “Output” screen appear. Here you can see that my table was successfully altered. If there are errors, it’ll let you know, and there will be A LOT of red (trust me, I saw tons of red prior to writing this post).
Bonus points, you can always find the Data Definition Language (DDL) in the DDL option (read up on this and the other types of SQL statements here).
As I see it, it looks like everything since the inception of this table has been recorded. I would imagine you could take this and build some sort of automation/orchestration script with it (along with the DDL from the other tables too).
Sample Code
Table as a Primary Key
CREATE TABLE ADMIN.RESTAURANTS
(
OBJECTID NUMBER ,
HSISID NUMBER ,
NAME VARCHAR2 (4000) ,
ADDRESS1 VARCHAR2 (4000) ,
ADDRESS2 VARCHAR2 (4000) ,
CITY VARCHAR2 (4000) ,
STATE VARCHAR2 (4000) ,
POSTALCODE VARCHAR2 (4000) ,
PHONENUMBER VARCHAR2 (4000) ,
RESTAURANTOPENDATE DATE ,
FACILITYTYPE VARCHAR2 (4000) ,
PERMITID NUMBER ,
X NUMBER ,
Y NUMBER ,
GEOCODESTATUS VARCHAR2 (4000)
)
TABLESPACE DATA
LOGGING
;
CREATE UNIQUE INDEX ADMIN.RESTAURANTS_PK ON ADMIN.RESTAURANTS
(
HSISID ASC
)
TABLESPACE DATA
LOGGING
;
ALTER TABLE ADMIN.RESTAURANTS
ADD CONSTRAINT RESTAURANTS_PK PRIMARY KEY ( HSISID )
USING INDEX ADMIN.RESTAURANTS_PK ;That is literally all did to set this up. Very straightforward, next I’ll walk through how to set up Foreign keys. Then I’ll show you what it looks like in the Data Modeler so you can visually see the newly-established relationships of the tables.
Establishing a Foreign Key
We’re still in the SQL worksheet (found in Database Actions). Both Inspections and Violations tables need a Foreign key; I’ll demonstrate with Inspections.
Right-click on the table and select “Edit”, just like I did when establishing a Primary Key. Next, navigate to the “Foreign Keys” option in the Table Properties.
It’ll be empty, but you’ll want to click the
“+” key (see the arrow). You’ll see “NEW_FK_1” appear under the Foreign Key table. If you click on that you can change the name to whatever your heart desires.
I then changed the Foreign Key name to something that I could remember (and something that would make sense at a later date); “Inspections_FK”. I then selected “HSISID” as the Local Column.
And automatically, the schema recognizes the Primary Key I previously established in the Restaurants table. I didn’t do that, that was all done for me automatically! Very cool.
After I click “Apply” you’ll see the output from this change. I can then return to the Foreign Keys option and voilà, the Foreign Key name has been changed. I’ll proceed to do the same to my “Violations” table as well.
Sample Code
Table as a Foreign Key
CREATE TABLE ADMIN.INSPECTIONS
(
OBJECTID NUMBER ,
HSISID NUMBER ,
SCORE NUMBER ,
DATE_ DATE ,
DESCRIPTION VARCHAR2 (4000) ,
TYPE VARCHAR2 (4000) ,
INSPECTOR VARCHAR2 (4000) ,
PERMITID NUMBER
)
TABLESPACE DATA
LOGGING
;
ALTER TABLE ADMIN.INSPECTIONS
ADD CONSTRAINT INSPECTIONS_FK FOREIGN KEY
(
HSISID
)
REFERENCES ADMIN.RESTAURANTS ( HSISID )
ON DELETE CASCADE
NOT DEFERRABLE
;Data Modeler Primer
This isn’t a “How-to” on the Data Modeler (Documentation here though), but rather just me taking a minute to share how you can visually see the relationships between tables. It definitely helped me better understand why I needed to establish Primary and Foreign Keys.
Andiamo!
From the Database Actions Launchpad, select “Data Modeler”.
Once there, you’ll see two drop-down menus. You’ll see your current Schema (Admin for me, in this case). In the second drop-down menu, you’ll see a list of available “Objects”. I’ll want to make sure that I have “Tables” selected.
You can drag these over one at a time, or select them all and drag all at once. They’ll appear in the Diagram Editor, with the relationships already included (since I already set up the Primary and Foreign Keys).
Expand these diagrams and you’ll reveal additional information. In this image, I’ve expanded the Inspections table to reveal Foreign Key details. I’ll do the same by expanding the bottom of the Violations table to reveal its Foreign Key. That little red arrow signifies that there is additional information hidden.
Okay, but what is this?
What we are looking at is a visual representation of the relationships between these tables. Everything is “linked” through the HSISID. My interpretation of this is that everything begins with the Restaurant table, and then the other two tables act as subordinates.
For a single Restaurant (which has a unique HSISID) there are many entries in both Inspections and Violations that use the HSISID repeatedly. In those cases, that HSISID is no longer unique. It is still important, necessary information.
And now that everything is linked, I’ll want to create a single query (SQL presumably) that when executed will return all relevant information from a single entry in the Restaurant table with all associated (HSISID) entries from the Inspections and Violations table, nested underneath it.
Clear as mud? Good. Hope this helps more than it hurts.
Now what
Next I’ll need to come up with something (in SQL perhaps) that I believe I’ll use in tandem with ORDS. From there I’ll work on building the html portion of this python + flask application so that the search is more intuitive (to the user) and elegant (for the Autonomous Database). Believe it or not, but it took about a week to wrap my head around all this key business. Needless to say, I’m slightly behind schedule. But expect more updates as I progress.
Hope you learned something.
Find me
HMU on: