Introduction to Databases

Most interactive websites save data in a database. An app with the ability to manipulate database data is often called CRUD – Create, Read, Update, Delete, because these are the functions we use to change the information in the database.

The types of web servers that support this function are usually ones with a full server back-end, as opposed to static website hosts like Neocities. Leprd.space actually offers a really easy way to host your own database app, which I plan to write more about in the future. 

Databases store information. The biggest benefit that databases give to your site is a way to store and access organized data. 

For this guide, I want to give a basic overview of what a database looks like and how it stores data. The easiest thing to do is just to think of a database as a collection of tables. 

Databases

Each database has at least one table, which basically look like spreadsheets or, well, tables.

Every table has a schema, which is the basic structure of columns and data types.

Below is an example table for a webring:

idsite_nameurlowner_name
1Working toward a better internethttps://sadgrl.onlineSadness

The table above has four columns:

  • id
  • site_name
  • url
  • owner_name

Using queries, it’s possible to create new items, read existing items, update items and delete them. 

Let’s look at another table in the same database, one which manages the users who can log in to edit the webring.

idusernameemailpassword
1sadnesssadness@sadgrl.onlinexxxxxx

It would look like this!

Notice how both tables have a column dedicated to ID. IDs are incredibly important because each table needs at least one column that is guaranteed to always be unique. This allows us to select an individual entry to delete from the database and know for sure we are selecting the one we intended.

To illustrate more, I’ll give some examples of what database back-ends might look like for storing certain types of data:

Blog:

iddatemoodtitlecontent
12/19/22calmMy Blog TitleHere is where my blog entry text is stored!

Using forms for data manipulation

Keep in mind, the database is only the back-end. It’s difficult to add items directly to a database without a front-end (usually a form) because you need to communicate with the database in a query language. The end-goal of using a language like PHP is to automate the communication with the database in a user-friendly way.

The form that would accompany the blog database above, might look something like this:

  • Title: [Input field]
  • Post [Large Input Field]

Even fields like checkboxes and select dropdowns can transport data to be inserted into a database.

Database Schema

Aside from column names, every schema has a set of rules that define a column. There are a lot of different rules, so I’ll just outline and define the ones I use the most:

NameDescription
NameThe name of the column
TypeThe data type of the column (integer, varchar, longtext, etc)
Length/ValuesThe allowed size of data in the column
DefaultThis is a dropdown of possible default values including “null” and custom values
NullMaking a column ‘null’ means the row can be updated or created without a value in this field
A_IUsed primary for the id column, this automatically increments a number for every entry in the table

In our blog submission form in the previous section, there’s no input for id because this is an auto-incrementing value. That means each new addition to the database will be assigned the next available ID without us having to do it ourselves.

There’s also no input for the date field because the current date is grabbed via PHP automatically at the time the form is submitted. 

phpMyAdmin

The software phpMyAdmin has been so incredibly helpful to me for learning how to use a database. This is basically open-source software you can install on any Apache webserver that acts as a back-end GUI for your database. If you have a site on leprd.space, you’re in luck because it’s part of the package!

For me, personally, it’s extremely difficult to work with a database without a GUI. It’s my impression (please correct me if I’m wrong) that most developers work with DBs through the command line. It’s not the worst, but I’m still in my transition phase where it makes me feel so comforted to just click a “delete” button next to an actual table row and watch it disappear in real time.

With phpMyAdmin, it’s extremely easy to:

  • set-up your database schema
  • view, edit and delete database entries
  • send SQL queries directly to the database

I’m going to include more about how to navigate phpMyAdmin in an upcoming tutorial!



Hey, thanks for reading! If you have any questions, you can leave me a comment and I always try to reply.

If you have any suggestions on how I can improve this article, please let me know!

This website is not monetized in any way. It purely exists to help others on their web-building journey. However, if you have the means to donate toward my server costs, I have a ko-fi account where I accept tips.

Leave a Comment