Every database has a schema. A database schema is the structure that represents the way the database is built. The database schema defines how data is stored in the database tables and how the relations among tables are associated.
In this lesson, we'll create visual representations of database schema and talk about the most common SQL database relationships:
Here's a visual representation of our record store's database schema so far:
This representation includes both of our tables, the properties of each table, and the relationship between the two tables.
At this point, our application is fairly simple. There's a table for
albums and a table for
songs. Each song belongs to an album, which we represent with a line between the
id property in the
albums table and the
album_id property in the
As we discussed in previous lessons, the relationship between these two tables is one-to-many. An album can have many songs but a song can only have one album. In this kind of relationship, it's fairly common for the "many" to be dependent on the one. If this is the case, songs should only exist if they belong to an album. However, one-to-many relationships can also exist without the "many" being dependent. An example might be cargo that belongs to a ship. The cargo can only belong to one ship, but there may be times when the cargo hasn't been loaded and doesn't belong to a ship yet.
Many-to-many relationships are more complicated because they involve a join table. Let's look at an example: a many-to-many relationship between
It's evident that an
Artist should be able to have many
Albums. However, a one-to-many isn't the best option here because an
Album could also have many
Artists. An example would be an album that's a compilation or a collaboration between multiple artists.
Let's take a look at a visual representation of the many-to-many relationship we'll build:
In this case, we have a join table called
albums_artists that stores the relationships between the
artists table and the
Sometimes a join table can be more complex. Some join tables also have other fields. An example might be a
checkout join table between
Books at a library. The
due_date of a book would most likely go on the
checkout join table.
Join tables can have their own descriptive names such as in the
checkout example above. However, it's also common to have the name be a combination of the two tables that are being joined. If this is the case, the names should be in alphabetic order. In other words, we'd always call the join table for
artists-albums. We need to use an underscore to delimit table names, not a character like a dash, which SQL will not accept.
One-to-one relationships are less common but are still very important. It's not required to use one-to-one relationships in this course but you may wish to experiment with them or have a project that would work best with this relationship.
Two tables have a one-to-one relationship when a row on one table is related to only one row on the other table. A common example is a relationship between
According to this schema, a customer can only have one account and an account can belong to only one customer. The relationship between the customers table and the accounts table represents a one-to-one relationship.
We will practice designing database schema using this free SQL Designer Tool. From now on, include a visual representation of your database schema that shows all tables, properties, and relationships between tables. In the next lesson, we'll go over the basics of using this tool.
Database schema: The structure that represents the way the database is built. It defines how data is stored in the database tables and how the relations among tables are associated.
One-to-one Relationship: Two tables have a one-to-one relationship when a row on one table is related to only one row on the other table.
One-to-many relationship: Two tables share a one-to-many relationship when a single row on one table can be related to many rows on another table.
Many-to-many relationship: Two tables share a many-to-many relationship when each row on each table can have many rows on the other table. This relationship requires use of a join table.
Join table: Used to manage all possible relationships in a many-to-many relationship, a join table contains individual entries.