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?
First determine what you want to accomplish with your database, i.e. track the classes of students
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.
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.
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.
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.
has many grades but each grade
belongs to a student.
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.