On your journey to become a full stack software developer you will have to learn about databases. You may have already began thinking about different database technologies like mySQL or Oracle 12c, and different techniques for utilizing these databases. You have to start thinking about how to interact with a database to store and retrieve data, and you also have to think about how to structure that information. In this blog I will discuss how to extract data out of a database utilizing SQL queries. But, first I will talk about a planning technique that many developers use to map out their data storage, by creating a schema.
What do I mean when I say schema? A schema is how you describe the technique of organizing a database and connecting the relationships between its different pieces or "entities." It will tell you how certain data relates to other data. I have been taught to think of a schema as a "blueprint for your database." A schema is something that you will design and plan during the starting stages of a project. This will give you a visual picture of your data before you begin writing code.
Let us imagine that we have been given the task of designing a database for a movie collection. For the purpose of this blog, we are only going to keep track of basic information about the movies: their genre and their director. A real life application of a movie database will require much more information, but for this demonstration I have chosen to keep things simple.
The schema in this blog was designed in dbdiagram.io an easy to use tool for drawing entity-relationship diagrams.
When you see or hear the term 'entity' while dealing with database diagrams, it is referring to a distinct unit in our diagram that has data associated with it.
In our movie collection diagram we have a movie entity, a director entity, and a genre entity. Our entities in our schema are each being represented by a table. In the table we list each of the attributes that are related to that table name.
We are discussing relational database systems and working with multiple tables. So we will need to make connections to show relationships between the tables. The connections shown on the diagram below represent a one to many relationship. Each movie will only have one director or one genre, but each genre can be related to many movies, and a director could have made many movies. Those connections are shown below with the line.
The dir_id from our director table will be connected to the id_director in our movie table, and the gen_id in our genres table is connected to our id_genre in our movie table. You will see how we can utilize those connections later to display data from our table.
Let us create our table, you use SQL commands to make your table in the database, the CREATE TABLE statement will tell the database to name the table accordingly. In our movie, director, and genres tables we stating the names of our rows and declaring what data type they will be. The ALTER TABLE commands will establish connections between our tables. Since the director and genre tables will be related to many different movies, we will add a foreign key to them and make a reference to our main movie table.
Now that our tables are designed we can fill them with some movies. We can use INSERT INTO to state the table we would like to enter data into. The we can enter the column name in parentheses and their VALUES after.
Now if we would like to view all the data from our movie table we can use the SELECT statement and the all * to select all the data of that table from the database.
This is what the data will look like. You can see in id_genre and id_director the corresponding number to the genre and director they are related to.
Lets look at similar syntax to grab all of the data from our other tables.
Now if we would like to see our movie table and include the genre and director. We can utilize our connections to the other tables and join the data to our main table. In the code below, the LEFT JOIN keyword returns all movies from the left table (movie), and the matched genre from the right table (genres). The same is done for the related data in the directors table.
You can see below that we have all the data on our movies. It is important to learn how to display data in your mySQL database because you may need to grab just the right kind of information for a website you are communicating with. Let us take a final look at our movies and all of their properties.
The journey to becoming a full stack developer can be long and sometimes it can seem overwhelming. But the accomplishment of learning a concept of computer science that has been around your whole life can be empowering. I hope you learned a little more about database planning and building a schema. I also hope you continue to learn more about different data storage/retrieval techniques.