Database Components  «Prev

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.

A simple database to display catalog sales
1) A simple database to store information about catalog sales might contain the following tables.

A list of customers with their contact information
2) A list of customers with their contact information.

A list of orders using the Customer ID number and the date.
3) A list of orders using the Customer ID number and the date.

A list of items ordered (order details), and
4) A list of items ordered (order details), and

A table of items available to be ordered
5) A table of items available to be ordered

The database might also include reports listing top-selling items or sales by month.
6) The database might also include reports listing top-selling items or sales by month.

An IS manager once asked me if I could create a database to track the computers at his company. This would be one way to do that:
7) An IS manager once asked me if I could create a database to track the computers at his company. This would be one way to do that:

A table listing each computer in the company with all hardware details
8) A table listing each computer in the company with all hardware details

A table listing the person who uses the computer, and where they sit,
9) A table listing the person who uses the computer, and where they sit,

A table listing all the software used at the company, and
10) A table listing all the software used at the company, and

A table listing which software can be found on which computer
11) A table listing which software can be found on which computer

Reports in the database might list all the people with each software package, or list computers by make or CPU speed.
12) Reports in the database might list all the people with each software package, or list computers by make or CPU speed.

Human resources department usually has plenty of information to keep track of about the employees of a company. This database design might give them a good start.
13) Human resources department usually has plenty of information to keep track of about the employees of a company. This database design might give them a good start.

It has a table listing employees by their ID number
14) It has a table listing employees by their ID number

A separate table listing confidential benefits information
15) A separate table listing confidential benefits information

A list of departments and department heads
16) A list of departments and department heads, and

A list of employees by department
17) A list of employees by department.

Some of the information in separate tables in this database could be stored in one database
18) Some of the information in separate tables in this database could be stored in one database.
However, it may be more convenient to separate the data if the different types of data will be updated separately.