In last week's post, we covered what a database is and how it can help your business. This week we will go one step deeper and dive into tables, columns, and records.
A database consists of at least one table but mainly includes multiple tables. Each table consists of rows and columns. If you think of a table as a grid, columns run from left to right vertically across the grid. The entries captured within these columns that run horizontally are called records or rows (these terms can be used interchangeably and refer to the same thing).
These can all be viewed as steps, getting more specific about the data in question as you move further down:
Step 1: A Database. Databases contain:
Step 2: Tables Tables contain:
Step 3: Columns Columns contain:
Step 4: Records/rows Records/rows contain:
Step 5: Fields
A database contains all of the steps below it, like a box. This could be your business box, for example. Once you open that up, you get to your tables boxes. This is the data your business aims to keep track of, broken down into particular categories. There could be a table for stock, one for customers, one for products, one for orders. Tables need to be as specific as possible to keep data tidy.
If we use a standard order as an example: Each customer that orders a product will have a name, address, and at least one order item, each product will have a price, each order placed will have a delivery date and order date. This is viewed as one order. However, putting them all under one spreadsheet becomes cluttered, as you can see below.
An excellent way to break it up is to ask yourself, can a client have more than one order? If the answer is yes, split them into two different tables, one for clients and one for orders. You will also need one for products. Next, ask, can an order contain multiple products? If the answer is yes, create a separate table for order items.
Let's dive even deeper.
Once you open up a table, you can add columns. Columns get even more specific. For example, the client's box will have details like the client's name, address, contact number.
How do you determine which columns to add first? Ask yourself: What exactly do I need to know about my customers to give them their products? Keep this info specific to the customer, not products or orders (leave these for their particular tables).
You can capture these column names through column types, such as dates, numeric, or textual data types. Column types help keep the data tidy and make searching a lot easier.
Unique keys
Because databases aim to create connections between data points, unique primary keys ensure no confusion occurs.
A primary key uniquely identifies each row in a database. This is why we include columns for client number, product number, and order number. No two rows can have the same primary key value. This allows you to select any row by knowing its primary key value.
Within DataGrows we recommend making your first column a unique identifier or a variation containing a unique identifier.
Once your database, tables, and columns are set up, you can build links. We do this by adding From Tables. This will be discussed in our next post.
Let's wrap it up
To recap, remember a database contains tables, tables contain columns, columns contain records or rows. Well done, you now know how to structure a basic database! Now let’s start building links.
If your business needs a solution like ours, but you are not sure how to set it up, reach out to a friendly staff member that will assist you in starting your data journey with DataGrows.