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 separately from the way that information is presented.
This makes information easier to:
- organize
- add
- change
- remove
- search
- filter
- retrieve
- reuse
Most importantly, databases give our information persistence. The data continues to exist independently of a particular page or interaction.
A Framework for Thinking About Data
As we work with databases, think about the process as:
STRUCTURE → STORE → INTERACT → RETRIEVE → PRESENT
STRUCTURE
What information do we need, and how should it be organized?
STORE
Where will that information live?
INTERACT
How can we add, view, change, or remove the information?
RETRIEVE
How can we ask for the information we need?
PRESENT
How will that information ultimately appear to a user?
This fits alongside some technologies you already know:
HTML → structure
CSS → presentation
JavaScript → interactivity
Database → stored content/information
The database isn’t necessarily what the user sees. It provides the information that an application can retrieve and present.
STRUCTURE: Collections, Records, Fields, and Values
Imagine we want to build an application about Athens coffee shops.
We could start with a list:
- 1000 Faces
- Bitty & Beau’s
- Buvez
- Cafe Racer
- Hendershot’s
- Jittery Joe’s
- Molly’s Coffee Company
- Sips Espresso Cafe
- The Rook & Pawn
- Walker’s Coffee & Pub
But a list of names isn’t particularly useful.
If someone were trying to decide where to get coffee, they might also want to know:
- neighborhood
- hours
- local or chain
- outdoor seating
- food availability
- study friendliness
- dog friendliness
Once we decide what information to collect, we can begin giving our data structure.
A spreadsheet gives us an easy way to visualize this structure:
| Name | Neighborhood | Local? | Outdoor Seating | Food |
| 1000 Faces | Downtown | Yes | Yes | Limited |
| Jittery Joe’s | Five Points | Yes | Yes | Yes |
Database Vocabulary
Collection: a group of related items.
Item / Record: one thing within the collection.
Attribute / Field: one type of information stored about an item.
Value: the actual information stored in a field.
In a spreadsheet:
Sheet → Collection
Row → Item / Record / Object
Column Heading → Attribute / Field / Key
Cell → Value
These concepts should also look familiar from JavaScript and JSON.
For example:
{ name: "1000 Faces",
neighborhood: "Downtown",
local: true
}Here, name, neighborhood, and local are our fields or keys. “1000 Faces”, “Downtown”, and true are their values.
Data Types
Not every value represents the same kind of information.
Common data types include:
String: text, such as “1000 Faces”
Number: numeric information, such as 4.8
Boolean: true or false
Date/Time: dates and times
Different database systems handle data types differently, but the important idea is that the type of information we want to store affects how we structure it.
Reality Doesn’t Always Cooperate
Data models are simplified representations of the real world.
Unfortunately, the real world can be messy.
Imagine a field called:
Outdoor Seating
Should the possible values be:
- Yes
- No
What happens if a coffee shop only has outdoor seating during certain times of year?
Maybe we need:
- Yes
- No
- Varies
- Unknown
Or perhaps the question itself isn’t useful enough to include.
Designing a database means making decisions about what deserves to become data and how reality should be represented.
There isn’t always one correct answer.
Categories and Tags: Creating a Taxonomy
Once we have a collection, we need to think about how users might want to organize and retrieve it.
For our coffee shops, Neighborhood could give us groups such as:
- Downtown
- Five Points
- Boulevard
- Normaltown
Other characteristics might cut across those groups:
- local
- chain
- dog friendly
- outdoor seating
- study friendly
- serves food
This gives us a useful distinction:
Categories organize. Tags describe across that organization.
A coffee shop might belong to a Downtown category while also having the tags Local, Outdoor Seating, and Study Friendly.
The system doesn’t inherently know which pieces of information should be categories or tags.
That’s an information-design decision.
Ask yourself:
What questions might a user want this data to answer?
Conceptual Model vs. Implementation
The structure we create before choosing a particular technology is our conceptual model.
Then we have to figure out how to implement that model using an actual system.
For example, a regular WordPress Post already provides:
- Title
- Content
- Excerpt
- Featured Image
- Categories
- Tags
We could map our coffee shop model onto those structures:
Coffee Shop Name → Title
Description → Content
Neighborhood → Category
Characteristics/Amenities → Tags
Photo → Featured Image
The fit won’t always be perfect.
That’s okay.
Conceptual model ≠ implementation.
Sometimes we build a system specifically around our data. Other times, we adapt our data to the structures provided by an existing framework.
Understanding that tradeoff is an important part of development.
CRUD
Once information has been stored, applications need ways to interact with it.
Most database interactions can be described using four operations:
Create — add a new record
Read — retrieve an existing record
Update — change an existing record
Delete — remove an existing record
Together:
CRUD
If you’ve ever created, viewed, edited, and deleted a Post in WordPress, you’ve already performed CRUD operations without calling them that.
Retrieval
Structured data becomes especially useful when we want to retrieve particular information.
Imagine asking our coffee shop data for:
All coffee shops Downtown
or:
All coffee shops tagged Outdoor Seating
or:
All Downtown coffee shops that also have Outdoor Seating
We shouldn’t have to create and maintain a separate copy of our information for every possible combination.
Instead:
Store once → retrieve as needed.
This is one of the major advantages of separating our data from its presentation.
Data vs. Presentation
The same stored record can appear in many different places.
A single WordPress Post might appear in:
- its individual Post page
- the main Posts listing
- a Category archive
- a Tag archive
- search results
Those aren’t five copies of the data.
They are different presentations of the same stored data.
That brings us back to our framework:
STRUCTURE → STORE → INTERACT → RETRIEVE → PRESENT
The better we structure our information at the beginning, the more useful things our applications can do with it later.