Describe how to design tables in a relational database by splitting data into multiple tables.
Designing Relational Databases using Access
Designing tables is the first step in creating a database. You will need to figure out:
What data you want to store
How many tables to store it in, and
How the tables will relate to each other
Before you begin figuring out how many tables you need, and what data will be stored in each table, you may want to consider the analysis you will be doing on your data in order to make sure that the fields you need are included. Remember that the relationships between tables will allow you to use related data from multiple tables in your analyses.
Sometimes the easiest way to figure out how to store your data is to find an example of similarly structured data.
Splitting Data into Related Tables
Most of the time, we have our data distributed across multiple tables, and those tables are "related" to each other in some way. For example, let's say we have a table for logging how well students do on their tests, and we include emails in case we need to email parents about slipping grades: There is one big thing to realize about these tables: they are describing relational data. They are describing data that relates to each other. When the same data is replicated across multiple tables, there can be interesting consequences.
It is often preferable to make sure that a particular column of data is only stored in a single location, so there are less places to update and less risk of having different data in different places.
If we do that, we need to make sure we have a way to relate the data across the tables.
The following series of images presents three different databases designed for different purposes.
The next lesson will explain how Access stores data in atable--the most basic database object.