Database Basics: Part 1
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017
Use these bookmarks to jump around the tutorial:
So, you've decided that you're interested in using a database in your web development, huh? Well, then the next step would be to learn about how the database works.
How the data is stored in a database is probably much simpler than you might think. Databases use a series of Tables to store the data. A table simply refers to a two dimensional representation of your data using columns and rows. For example:
So, then , how does the database keep things straight? Well, first each database table is given a unique name. Without a unique name the DBMS (DataBase Management System) would get very confused.
Next, each column in the table is given a unique name. In our example above it would be something like first_name, last_name, email. This doesn't mean each column that you name has to be unique within the entire database. It only has to be unique within the table you have created. Also notice that the names don't use any spaces. When naming tables and columns be sure to keep it simple with letters and numbers. Spaces and symbols can be illegal characters that will mess up the whole works, so if you need to clarify a name use the "_" instead of spaces.
Let's update our table now:
Table name: contacts
back to top
The next thing to understand about your table is the Primary Key. The Primary Key simply refers to a column in your table that is guaranteed unique. The Primary Key is then used for the purposes of indexing your table which makes it much more efficient to search, sort, link, etc.
So what is the Primary Key in our example? Good question. There is none. In our example, there is nothing that is going to be guaranteed unique. Obviously there are many people that share the same last name and/or first name that may be added to the database in the future. The email address is much more likely to be unique but what if two people shared the same email?
To avoid the uncertainty of using a data column as a primary key, many developers will create their own column which contains a computer generated unique number, an ID number of sorts. This way you don't ever have to worry about its uniqueness since the database knows not to ever use the same number twice.
Table name: contacts
How many tables should I use? That depends on how you can logically break down your data. There is no limit to the number of tables, or columns for that matter, you can create. Keep in mind, though, that one huge table will be very inefficient while a bunch of little tables can be nearly impossible to keep straight. The best solution usually lies somewhere in the middle.
Here's an example. Let's say our contact table stores contact information for a subscription database for HTML Goodies. Now we need to store what newsletter(s) each person wants to subscribe to. We could simply add another column in our contact table that would store the name of the newsletter. This would allow us to save the information we need but cause names and email addresses to be duplicated, once for each different newsletter a person subscribes to. That would be highly inefficient.
What about making a second table for the names of the newsletters. This way each newsletter name and description would be stored only once.
Table name: newsletters
|1||Goodies to Go||A newsletter for HTML fans.|
|2||Design Goodies||A newsletter for web & graphic designers.|
back to top
That's great. I have the people in one table and the newsletters in another table. How the heck am I supposed to know who is subscribed to what?
This is the best part. This is where the Relational Database gets its name. Relational Database? You never mentioned that.
So, far you have learned some of the basic elements of a database. Now you will learn how to take those basic elements and make them relate to one another so that the information you are storing remains logically linked together. Hence the new and improved Relational Database.
Now, using our example above we have a table of subscribers (contacts) and a table of newsletter information (newsletters). So, how do we know who subscribed to what? We make another table that links the two tables we already have together.
Table Name: contact_newsletter_link
This table then places a link between the contact table and the newsletters table using each tables unique ID number. So, by referring to the table above you can see that John Smith has subscribed to Goodies to Go, Paul McCartney subscribed to Goodies to Go & Design Goodies and Bill Murray subscribed to Design Goodies.
But what about that Primary Key thing you mentioned earlier? The ID numbers are used more than once. Nothing is unique.
That's true. In this instance we are using Foreign Keys. A Foreign Key basically means that the number used in a Foreign Key column is not necessarily unique to the table it is in but is unique to the table it is referring to. In this case contact_id is unique to the contact table but not necessarily unique to the contact_newsletter_link table.
We now have the basics of a relational database.
back to top
Now that you have an understanding of the basic structure of a relational database we will examine the tables and the data a bit more closely.
In Part 2 of this series we will:
Learn about the most common data types
Learn some basic SQL commands to create tables
Learn how to create a database and tables with a tool like Access