Ky’s (Somewhat Poignant) Guide to Relationships

(Also inconsistently referred to as Ky’s Guide to Starting Over 1.5 or Ky’s Guide to Socially Disruptive Marble Habits in future annotations and subsequent editions)

All the Doll’s Eyes

I breathed a heavy sigh of relief as I stared at the screen. I had just outbid Marbelous_Mable_54 for the pristine, late-19th century Razorback Sulfite that was put up for auction late in the night two weeks prior. I had only noticed the obscure listing because I was in the habit of staying up into the early morning hours scanning r/marble_life, and working though the last stages of RB180 — Database Foundations. Admittedly, the former taking focus over the latter at some points.

The current bid on this beauty was sitting at $74, but I wasn’t too worried. I had just come into a healthy windfall of cash resulting from the sale of my trebuchet to a troupe of medieval combat reenactors anxious to up their siege warfare tactics, which meant I had some change to spare. Sure rent was coming due and there were some carpenter ant induced weaknesses growing in the walls, but ever since I lost out on the Kuhnershutte Green Latticinio I had developed, what some friends, family members, and even the ocelot under the kitchen table referred to as, “an unhealthy obsession that is interfering with your ability to act like a functional adult” or something along those lines. There were some bits about healthy lifestyle choices, eroding friendships, burdensome brothers, but I truthfully couldn’t focus too much on what they were saying with such a marble on the line. Had I stopped taking phone calls? Sure. Had I been calling out sick from work with some regularity? Absolutely. Was I turning away visitors dropping by unannounced to check up on me? With some gusto. Was I obsessed? Clearly, I could stop at any point. Any point after I had that Razorback Sulfite in my possession.

It was as I was contemplating the rare, new addition to my collection that I realized I would have to document the purchase, not only as a point of pride to look back on, but also because asset tracking is kind of a big deal in the marble collecting world.

I went and grabbed the records that I had been keeping for my collection thus far; after a few minutes I realized that my current system of data collection (scrawling mismatched notes in a decaying leather-bound journal stuffed with sheets of loose leaf and the occasional cocktail napkin) was a very unstructured way of keeping all of that information. A brief survey revealed that there were duplicate entries, and records for marbles that I didn’t even have anymore. The quality and integrity of the data was somewhat suspect. With shame I realized that this record keeping is unacceptable for a prideful marble collector. I mean, how could I prove to some dubious collector that my genuine M.F. Christensen and Son Peppermint Swirl wasn’t just some imitation?

How to build a successful relationship

Fortunately for me, I had nearly completed the Launch School course on Database Foundations, and had learned quite a bit about how to manage record keeping so that the data is structured and regulated in such a way that the quality of information is held to a high standard and information integrity is maintained. I could create a relational database using PostGreSQL!

This was going to involve some hands-on work, but I thought it would likely be worth it in the end. I turned my laptop towards me so I could see the live auction and set to work with the mess of notes I had. The first step would be taking the chaotic mess of notes and putting it into a more readable format. I combed through the tangle of entries and parsed out the key information that was recorded for each.

When the data was laid out in a tabular format, with each column representing a specific attribute, and each row representing a single record, it was a bit easier to read. At least, somewhat more organized. But to follow the relational model of data, I needed to go a step further. The data needed to be normalized; they needed to be separated into distinct tables with specific relationships defined between each. Staring at the tabular data though, it was difficult to conceptualize what needed to be extracted and to where. This was a job for an Entity Relationship Diagram (ERD); with this, I could take a high-level view of the database and think about the entities and relationships involved.

This was a better, but to really make it worthwhile I needed to define exactly what types of relationships existed between certain tables and make that clear on the ERD. For example, did each marble only have one color? If it had more than one color, that would need more than one entry, which would be repetitious. It would make more sense to have a many-to-many relationship so that each marble could be associated with more than one color and vice versa. I set to work identifying the entities (tables) I would need and the relationships that would connect them all.

Right, with that in place, it should be easier to write the code needed to build the schema of the database. I could see easily now that certain pieces needed to be added to make certain relationships possible. The primary keys for each table would be there to serve as the unique identifier for each row in the table. The primary keys could be referenced in other tables through the use of a foreign key. In this way, I could tie the marble_type to the marble, by referencing the PK (id column) of marble_type in the marble_type_id column as a FK. Makes sense, right? No? That’s fine, stick with me. It’ll all come together.

I checked through the table data using some handy PostGreSQL meta commands to make sure everything was set up as I expected.

Sure enough, the schema looked better than fresh chips out of the fryer. My ERD work had paid off.

With the schema of the database completed, all that I needed to do now was insert the data entries into each respective table. First things first, I decided to populate the tables that would contain information needed to reference for the marble, like color, type, and manufacturer.

Everything looked good at that point, so the next piece was to work through the catalogue of marbles in my tabular data and start inserting entries in the marbles table for each. The entries for acquisition_details and manufacturing_details would need to be created for referencing in the marbles entry. They would also need to have the correct values inserted for each foreign key and the join table that created the many-to-many relationship between marbles and marble_type would need to be updated as well.

With a self-satisfied grin, I swapped over to the auction, only to feel my self-congratulatory mirth congeal and go all ‘ooky’. Marbelous_Mable_54 had swooped in while I was building the database for my collection and outbid me. It was time to show her I couldn’t be chased off by a triple digit price tag. I raised the bid to $250 and submitted it with only the vaguest unease that I was about to spend the equivalent of 125 petrol station Cornish pasties on a single marble.

Sure that my latest bid should keep Mable at bay, I spent some time pecking away at the keyboard, inserting the rest of the data for my marble collection.

Success in all things

The endeavor appeared to be a success, after all, I had been able to input all of my data into the tables, so the constraints and keys were probably keeping the data quality intact. The hard work was largely behind me. But what good was a shiny new digital database if I couldn’t use it to look up the information about my precious marbles?

It was time for query. I decided to first go for a basic query to get all the information stored for a marble on one row of the marbles table.

Hmmm… that doesn’t look quite right does it? Of course! It’s because I need to join the other tables in to get the relevant information that is being referenced by the foreign keys!

Those results were pretty good too, but, while I was at it, why not make use of some aliasing to make the query more concise and return more understandable results?

Using aliasing and joining, combined with the power of different SQL clauses to filter results could provide some seriously customizable reporting. These different clauses could be sprinkled into the query like dried shrimp flakes on a good tea cake, just enough would take it from a flat square of passable biscuit to a scrumptious midnight meal. Too much though, and you are left with that Botany Bay at low-tide feeling in your mouth.

I was feeling pretty good about my use of SQL clauses and the well-defined scope of results that I was seeing as a result of my efforts. It made searching through records a great deal less painful than combing through the old ledger. I decided to really see what I could do…

That last one left a shrimpy aftertaste in my mouth, but I couldn’t quite tell if I disliked it or not. Some cakes might call for more shrimp flakes. To each their own though, that’s the way of the world after all.

Finally, the database was created. The schema had been set up to maintain the quality and integrity of my records. I had taken a disorganized bin-fire of unorganized records and transferred them into a neat, concise system of digital records.

Satisfied with my project, I turned my attention to the online auction for the Razorback Sulphite; the timer had crept down to just 2 hours remaining. Now that I was an expert on relationships, my data was in order, and the marble could be catalogued properly i̵f̵ when I won it.

Just then there was a knock on the door and a muffled voice wormed its way through the threshold.
“…just wanted to see if you wanted to perambulate under the eaves of this beautiful summer’s day?… packed a picnic and everything… like a plan, dear boy?”

It was my old friend Reginald, Ornithopter enthusiast and friend of octopi everywhere. Good chap, and always up for a spot of fun.

Naturally, as a qualified expert on relationships, I knew exactly what to do! I got up, shut the blinds and returned to my computer to repeatedly refresh the auction site, making plans all the while for the impending arrival of my new precious.

To be continued or not… We’ll see.

Until next time or not… We’ll see.

-Ky