Sign in or start a free trial to avail of this feature.
7. Database Tables
All databases are made up of tables. In this lesson, we’ll explain the fundamental properties of a table, and discuss the important distinction between fact tables and dimension tables.
The goal of this lesson is to learn about the principles of database tables, including their structure and the different roles tables can fill in a database.
Properties of a Table
Each table in a database records information on some entity, such as fee earners in a professional services company. A table consists of a series of rows and columns. Each column represents a field, or an attribute. Each column contains a piece of information about the entity, like a fee earner’s name or division. Each row in the table represents a record, or a tuple. A record is an example of the entity, such as an individual fee earner.
Any database table should have a primary key. This is a field which uniquely identifies every record in a table. In other words, it is a field with no duplicate values. Primary keys help prevent you from identifying an incorrect record, which can happen with duplicate values. In a small table, one field may already be a primary key naturally. Other times, you’ll need to create a field (usually some sort of ID field) to act as a primary key.
Fact and Dimension Tables
A fact table is a table containing data on a business process, for example, sales or purchases. A record represents an event, and a field represents a characteristic of the event. A dimension table is a table containing data on an object or thing, for example, products or customers. A record represents an individual object, and a field represents a characteristic of the object.
Generally, fact tables have a tall and thin layout, while dimension tables have a short and wide layout. Understanding the difference between fact and dimension tables is useful when studying database layouts, which we’ll see in a later lesson.
In the previous lesson, we explored some important database concepts at a higher level.
In this lesson we'll learn about the principles of database tables.
We'll look at the structure of database tables and the different rules that tables can fulfill within a database.
Each table in a database consists of a series of rows and columns. For example here we have a table holding information about the fee earners of a professional services company. In this table each column contains a different piece of information about the fee earners.
In this context a column is referred to as a field or an attribute.
All the data in a particular field must be of a single type.
We'll learn more about this in the next lesson.
Each row in the table represents an individual fee earner.
In a database, a row is referred to as a record or a tuple.
If we look at this table, we can see that each fee earner has a unique name while the other fields contain duplicate values.
As a result, we can say that the name field is the primary key for this table.
A primary key is a field which uniquely identifies all the records of a table. If we are told the name of a fee earner, we know exactly which record in the table refers to that fee earner. If we were told the fee earner's rank, we would not automatically know which record the rank refer to as many fee earners have the same rank. Every table in a database should have a primary key. This is not an absolute requirement but it is best practice.
Having a primary key prevents you from identifying or selecting an incorrect row, which could happen if all the fields contain duplicate values. This is especially important when you're joining tables together, which we'll see later on. Sometimes one of the fields in your table will naturally be a primary key.
Other times you'll need to add a primary key field of your own.
For example if we're working with a large company where multiple employees have the same name, a table like this could include an employee ID field which could serve as a primary key.
There are many different ways of classifying database tables into types.
One of the most common involves ascribing each table as either a Fact table or a Dimension table.
A fact table contains data corresponding to some business process. For example in a retail company sales or purchases could typically be described using a fact table. In a fact table each record represent a single event. And each field represent some aspect of that event. So in a sales table, each row would represent a sales transaction, and each column would represent some aspect of that transaction, such as selling price, the quantity of product sold, and the customer the product was sold to.
A dimension table contains data corresponding to objects that are involved in a business.
In a retail company, things that could be measured in a dimension table would include products, customers, stores and so on.
Each record in a dimension table represents an individual object and each field represents some attribute of that object.
So in a product table, each row would represent an individual product, where there could be columns for the product's name, its cost and its selling price. In a typical database, fact tables have a tall and thin layout, with a high number of rows and relatively few columns.
By contrast dimension tables tend to have a short and fat layout with fewer rows and more columns.
The intuition for this is easy to understand. In a retail company, we would expect each product to be sold many times. So it makes sense that a table containing sales transactions would have many more records than a table containing product details.
Also each of the products would have many associated fields containing various pieces of information about the product, but we wouldn't want to record all those details every time the product is sold. Instead, we would just record the product name and use the product table to identify the rest of the information about the product. As a result, fact tables are likely to have fewer columns than dimension tables. Let's stop the lesson here. In the next lesson we'll learn more about how data is stored in tables, focusing specifically on the concept of data types.