{"id":4969,"date":"2026-08-16T21:02:19","date_gmt":"2026-08-16T21:02:19","guid":{"rendered":"https:\/\/nmi.cool\/advweb\/?page_id=4969"},"modified":"2026-08-16T21:05:00","modified_gmt":"2026-08-16T21:05:00","slug":"course-notes-databases","status":"publish","type":"page","link":"https:\/\/nmi.cool\/advweb\/course-notes-databases\/","title":{"rendered":"Course Notes: Databases"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\"><strong>Why Databases?<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">So far, much of the information we have worked with in web development has lived directly inside HTML documents, JavaScript arrays and objects, JSON files, or external APIs.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That works great for smaller projects, but applications often need a better way to manage information as they grow.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Databases allow us to <strong>store information separately from the way that information is presented<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This makes information easier to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>organize<\/li>\n\n\n\n<li>add<\/li>\n\n\n\n<li>change<\/li>\n\n\n\n<li>remove<\/li>\n\n\n\n<li>search<\/li>\n\n\n\n<li>filter<\/li>\n\n\n\n<li>retrieve<\/li>\n\n\n\n<li>reuse<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Most importantly, databases give our information <strong>persistence<\/strong>. The data continues to exist independently of a particular page or interaction.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>A Framework for Thinking About Data<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">As we work with databases, think about the process as:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>STRUCTURE \u2192 STORE \u2192 INTERACT \u2192 RETRIEVE \u2192 PRESENT<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>STRUCTURE<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">What information do we need, and how should it be organized?<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>STORE<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Where will that information live?<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>INTERACT<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">How can we add, view, change, or remove the information?<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>RETRIEVE<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">How can we ask for the information we need?<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>PRESENT<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">How will that information ultimately appear to a user?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This fits alongside some technologies you already know:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>HTML \u2192 structure<\/strong><strong><br><\/strong><strong>CSS \u2192 presentation<\/strong><strong><br><\/strong><strong>JavaScript \u2192 interactivity<\/strong><strong><br><\/strong><strong>Database \u2192 stored content\/information<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The database isn&#8217;t necessarily what the user sees. It provides the information that an application can retrieve and present.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>STRUCTURE: Collections, Records, Fields, and Values<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine we want to build an application about Athens coffee shops.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We could start with a list:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>1000 Faces<\/li>\n\n\n\n<li>Bitty &amp; Beau&#8217;s<\/li>\n\n\n\n<li>Buvez<\/li>\n\n\n\n<li>Cafe Racer<\/li>\n\n\n\n<li>Hendershot&#8217;s<\/li>\n\n\n\n<li>Jittery Joe&#8217;s<\/li>\n\n\n\n<li>Molly&#8217;s Coffee Company<\/li>\n\n\n\n<li>Sips Espresso Cafe<\/li>\n\n\n\n<li>The Rook &amp; Pawn<\/li>\n\n\n\n<li>Walker&#8217;s Coffee &amp; Pub<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">But a list of names isn&#8217;t particularly useful.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If someone were trying to decide where to get coffee, they might also want to know:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>neighborhood<\/li>\n\n\n\n<li>hours<\/li>\n\n\n\n<li>local or chain<\/li>\n\n\n\n<li>outdoor seating<\/li>\n\n\n\n<li>food availability<\/li>\n\n\n\n<li>study friendliness<\/li>\n\n\n\n<li>dog friendliness<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Once we decide what information to collect, we can begin giving our data <strong>structure<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A spreadsheet gives us an easy way to visualize this structure:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Name<\/strong><\/td><td><strong>Neighborhood<\/strong><\/td><td><strong>Local?<\/strong><\/td><td><strong>Outdoor Seating<\/strong><\/td><td><strong>Food<\/strong><\/td><\/tr><tr><td>1000 Faces<\/td><td>Downtown<\/td><td>Yes<\/td><td>Yes<\/td><td>Limited<\/td><\/tr><tr><td>Jittery Joe&#8217;s<\/td><td>Five Points<\/td><td>Yes<\/td><td>Yes<\/td><td>Yes<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Database Vocabulary<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Collection:<\/strong> a group of related items.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Item \/ Record:<\/strong> one thing within the collection.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Attribute \/ Field:<\/strong> one type of information stored about an item.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Value:<\/strong> the actual information stored in a field.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In a spreadsheet:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Sheet \u2192 Collection<\/strong><strong><br><\/strong><strong>Row \u2192 Item \/ Record \/ Object<\/strong><strong><br><\/strong><strong>Column Heading \u2192 Attribute \/ Field \/ Key<\/strong><strong><br><\/strong><strong>Cell \u2192 Value<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">These concepts should also look familiar from JavaScript and JSON.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>{ name: \"1000 Faces\",\n  neighborhood: \"Downtown\",\n  local: true\n}<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Here, name, neighborhood, and local are our fields or keys. &#8220;1000 Faces&#8221;, &#8220;Downtown&#8221;, and true are their values.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Data Types<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Not every value represents the same kind of information.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Common data types include:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>String:<\/strong> text, such as &#8220;1000 Faces&#8221;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Number:<\/strong> numeric information, such as 4.8<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Boolean:<\/strong> true or false<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Date\/Time:<\/strong> dates and times<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Different database systems handle data types differently, but the important idea is that <strong>the type of information we want to store affects how we structure it.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Reality Doesn&#8217;t Always Cooperate<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Data models are simplified representations of the real world.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Unfortunately, the real world can be messy.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine a field called:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Outdoor Seating<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Should the possible values be:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Yes<\/li>\n\n\n\n<li>No<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">What happens if a coffee shop only has outdoor seating during certain times of year?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Maybe we need:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Yes<\/li>\n\n\n\n<li>No<\/li>\n\n\n\n<li>Varies<\/li>\n\n\n\n<li>Unknown<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Or perhaps the question itself isn&#8217;t useful enough to include.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Designing a database means making decisions about <strong>what deserves to become data and how reality should be represented.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There isn&#8217;t always one correct answer.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Categories and Tags: Creating a Taxonomy<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Once we have a collection, we need to think about how users might want to organize and retrieve it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For our coffee shops, <strong>Neighborhood<\/strong> could give us groups such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Downtown<\/li>\n\n\n\n<li>Five Points<\/li>\n\n\n\n<li>Boulevard<\/li>\n\n\n\n<li>Normaltown<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Other characteristics might cut across those groups:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>local<\/li>\n\n\n\n<li>chain<\/li>\n\n\n\n<li>dog friendly<\/li>\n\n\n\n<li>outdoor seating<\/li>\n\n\n\n<li>study friendly<\/li>\n\n\n\n<li>serves food<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This gives us a useful distinction:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Categories organize. Tags describe across that organization.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A coffee shop might belong to a <strong>Downtown<\/strong> category while also having the tags <strong>Local<\/strong>, <strong>Outdoor Seating<\/strong>, and <strong>Study Friendly<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The system doesn&#8217;t inherently know which pieces of information should be categories or tags.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That&#8217;s an <strong>information-design decision<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ask yourself:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What questions might a user want this data to answer?<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conceptual Model vs. Implementation<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The structure we create before choosing a particular technology is our <strong>conceptual model<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Then we have to figure out how to implement that model using an actual system.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, a regular WordPress Post already provides:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Title<\/li>\n\n\n\n<li>Content<\/li>\n\n\n\n<li>Excerpt<\/li>\n\n\n\n<li>Featured Image<\/li>\n\n\n\n<li>Categories<\/li>\n\n\n\n<li>Tags<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">We could map our coffee shop model onto those structures:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Coffee Shop Name \u2192 Title<\/strong><strong><br><\/strong><strong>Description \u2192 Content<\/strong><strong><br><\/strong><strong>Neighborhood \u2192 Category<\/strong><strong><br><\/strong><strong>Characteristics\/Amenities \u2192 Tags<\/strong><strong><br><\/strong><strong>Photo \u2192 Featured Image<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The fit won&#8217;t always be perfect.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That&#8217;s okay.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Conceptual model \u2260 implementation.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes we build a system specifically around our data. Other times, we adapt our data to the structures provided by an existing framework.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Understanding that tradeoff is an important part of development.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>CRUD<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Once information has been stored, applications need ways to interact with it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Most database interactions can be described using four operations:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Create<\/strong> \u2014 add a new record<br><strong>Read<\/strong> \u2014 retrieve an existing record<br><strong>Update<\/strong> \u2014 change an existing record<br><strong>Delete<\/strong> \u2014 remove an existing record<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Together:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>CRUD<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;ve ever created, viewed, edited, and deleted a Post in WordPress, you&#8217;ve already performed CRUD operations without calling them that.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Retrieval<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Structured data becomes especially useful when we want to retrieve particular information.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine asking our coffee shop data for:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>All coffee shops Downtown<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">or:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>All coffee shops tagged Outdoor Seating<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">or:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>All Downtown coffee shops that also have Outdoor Seating<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We shouldn&#8217;t have to create and maintain a separate copy of our information for every possible combination.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Instead:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Store once \u2192 retrieve as needed.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is one of the major advantages of separating our data from its presentation.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Data vs. Presentation<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The same stored record can appear in many different places.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A single WordPress Post might appear in:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>its individual Post page<\/li>\n\n\n\n<li>the main Posts listing<\/li>\n\n\n\n<li>a Category archive<\/li>\n\n\n\n<li>a Tag archive<\/li>\n\n\n\n<li>search results<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Those aren&#8217;t five copies of the data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">They are different <strong>presentations of the same stored data<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That brings us back to our framework:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>STRUCTURE \u2192 STORE \u2192 INTERACT \u2192 RETRIEVE \u2192 PRESENT<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The better we structure our information at the beginning, the more useful things our applications can do with it later.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Why Databases? So far, much of the information we have worked with in web development has lived directly inside HTML documents, JavaScript arrays and objects, JSON files, or external APIs. That works great for smaller projects, but applications often need a better way to manage information as they grow. Databases allow us to store information &hellip; <a href=\"https:\/\/nmi.cool\/advweb\/course-notes-databases\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Course Notes: Databases<\/span><\/a><\/p>\n","protected":false},"author":18,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-4969","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/pages\/4969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/users\/18"}],"replies":[{"embeddable":true,"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/comments?post=4969"}],"version-history":[{"count":2,"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/pages\/4969\/revisions"}],"predecessor-version":[{"id":4971,"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/pages\/4969\/revisions\/4971"}],"wp:attachment":[{"href":"https:\/\/nmi.cool\/advweb\/wp-json\/wp\/v2\/media?parent=4969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}