DEV Community

Joe Sembler
Joe Sembler

Posted on

The Fundamentals of Database Structure

Where to begin?

If you're wondering where to begin when creating a database just ask yourself: what is the purpose of this database? What data do you want to store in your database? How many tables will you need to store your data? What type of data will each table include? How will each table interact with one another?

Purpose.
First determine what you want to accomplish with your database, i.e. track the classes of students

Data.
In order to track the classes of students you will need to not only store the classes of each student, but also the name of each student. The student itself may be a table that includes information such as the student's name, date of birth, GPA, etc. You may also want to include information about the class such as: the name of the class, the instructor, time and dates the class meet, etc.

Data Types.
Just like when programming in JavaScript, or any other programming language, there are different data types to consider. Some common data types are numbers, strings and boolean.
Pictured below is how our tables may look with their different data and data types. You will notice each table has its own id. This is known as the primary key and is what allows our program to distinguish between each item in each table.

how our tables may look with their different data and data types

Table Organization.
Table organization is key. How the tables interact with one another must follow the logic of how you want your database to be organized. This sounds trivial but can be tricky.
For example, a student has many classes but a class also has many students.

Student - classes relationship

This means that we must have a join table, or a table that contains common fields from two or more tables. In our case it will be two fields: classes and students. These fields are known as foreign keys. Our join table will be called grades and will store all of the grades for each student and each class.

A student has many grades but each grade belongs to a student.

student-grades relationship
A class has many grades whiles grades belong to a class.

class - grades relationship
It is important to note that students has many classes through grades and vice versa - classes has many students through grades.

all table relationships

Conclusion

This is just the beginning of database structure, however, these very building blocks are what are used to create the most complicated databases on the internet.

Discussion (0)