About to tackle a huge task? Like building a full stack app and your own database. You're going need a plan, that's for sure. In the next few paragraphs I will aim to share with you some basic/ introductory insight in to how you can make a plan to organize your data with your schema design.
Let's start with what is a schema. When the word schema was first introduced to me it was in class. It is not a word I used before and doesn't seem to be a word I use often in my normal (non-coding) interactions. However, when I learned that schema can be analogous to the word blueprint, things started to click. I also quickly realized that word schema is use a lot in the development world, but it did not originate here nor is it confined to the development world.
If you google "schema definition" (at least at this point in time) the first definition you'll see says:
"a representation of a plan or theory in the form of an outline or model." -from Oxford
Again, no profound software development insight here... Except that we're talking about an idea. Or a plan. Something abstract that represents something else. So this was a lightbulb moment. A schema is what we put our tables on in a relational database management system, more on that in a moment, which acts as a blueprint for how we want to organize our data in our database. Schemas also address how we want our data to relate to each other if at all.
One of the reasons I enjoyed learning about schema design is because I like a good plan. Building out your schema is a great way to make a solid plan for how to house your data in your database before you even interact with it. I'll be specifically talking about what I had mentioned before, designing a schema in a relational database management system (RDBMS) like mySQL.
These RDBMS, which is much easier to type, are programs that allows us to manipulate (e.g create, update) relational databases; most of which use the SQL language to access their database. The relational part comes in to play with the tables and how each table might relate to one another.
We'll look into three different kinds of relationships between tables that we can utilize when designing our schema. The first relationship we'll start with is called a one-to-many relationship.
Here we have one album that can have many songs on it. This schema shows two tables where our 'one' in the relationship (the album) has a foreign key on the 'many' of the relationship (the song). The 'one' in the relationship is always puts its foreign key on the table of the 'many'. What this does is it references the data from the one's id rather than creating new data to (repetitively) store in the table of the 'many'. Think copy by reference verses copy by value. Foreign keys are great tools that allow us to show relationships between data and prevent ourselves from having to write code/data twice.
Next we'll look at a many-to-many relationship. As you noticed there are three tables here. We have our table of students, our table of teachers, and then a third table that they are both connected to. This is called our join table. It joins the primary keys (id) from both students and teachers tables and again allows us to not repeat ourselves when storing data, as well as prevents multiple pieces of data to be stored in the same box. Advantages here: dry code, clear relationships, easier data manipulation.
And finally we have our one-to-one relationship. We are online at our favorite store about to check out and our shopping cart is full of items that will hopefully make our lives even better. Well, each of these shopping cart items has quite a bit of information attached to it. The brand, the size, where it was made, etc. This information can be organized in a one-to-one relationship. These two tables are sharing id's because they are referencing the same thing but holding different information about this piece of data. Efficient and effective, what more could you ask for?
These are three must-know relationships when designing your next amazing schema. With a some planning and organization you can make sure that your data is stored in the best possible way by paying careful attention to how your data might best relate to itself. As always, keep that code dry and remember: design/plan now and save time later!
Latest comments (0)