What are the things that you need to consider in creating a database?

Are you looking to design a database? Whether it’s for your workplace or home project, learning how to design a database is a valuable skill. Let’s take a look at six easy steps on how to design a database.

Table of Contents

• 1 – What Is Your Purpose?
• 2 – List What You’re Storing Data About
• 3 – Define The Attributes
• 4 – Develop The Relationships
• 5 – Decide On A Unique Identifier
• 6 – Include The Identifiers In Each Table
• Final Tips On How To Design A Database

1 – What Is Your Purpose?

The first place to start when learning how to design a database is to work out what the purpose is.

Why are you designing a database? No, this isn’t just because someone told you do, or because your software needs it. What is the aim of the database? What is it storing?

Thinking about the aim of the database for the first step will help you make decisions about it later on. Some examples of database purposes are:

• Storing customer transactions on your website
• Storing definitions of teams and players
• Storing reference data for your website

Sure, it’s possible for a database to store a large amount of different kinds of information, but if you start with the purpose, it will make this process easier.

Action: Write down the purpose for creating this database.

2 – List What You’re Storing Data About

Alright, so you’ve got your purpose. Now it’s time to decide what it’s going to store.

This is where we go into a little more detail on the database. To give an example, let’s say we’re creating a database to store customer transactions on your website that sells books (a lot of examples on the net use the word “widget”. I personally hate the word widget. It’s useless. Just use the word “object” or “item” or “product”. So I chose books. Rant over).

So, what do we need to know? What do we need to store data about? Start making a list. For example, to sell books, we might need to know:

• Books – What books we have for sale
• Customers – Who our customers are
• Sales – A list of sales of these books

To keep it simple, I won’t include other possibilities such as promotions or authors or anything. Write this list as a list of “items”. See how I’ve used “books, customers, sales” above?

Also, make them plural. It’s easier to think of what to store data about if you think of plural. This could be because each row in a table represents one of these items, and a table contains many of them.

Action: Write a list of plural items that you need to store data about. These will be your tables.

3 – Define The Attributes

Now we have our list of items, or tables, that we’re storing data about. The next step is to decide what to store about each of these.

Think about what you need to know about each of these items. What would people want to know? What do you want to know? What represents these items?

We can use the book database again. Using these tables, I can list several attributes:

• Books – title, author, price
• Customers – first name, last name, address
• Sales – sale date, customer, book, price

This is another simplification of a real database, but it explains the point. As shown above, the “books” table would need a few attributes. For each book, we need to know the title, the author, and the price of the book. These will become attributes, or columns, in the table.

Action: List down what attributes are needed for each of your tables.

4 – Develop The Relationships

We have our tables with our columns, or attributes. At the moment, they are just tables, sitting by themselves. To make them work, we need to link them together.

This is what a relationship is. No, I’m not talking about dating, I’m talking about a link between two tables in a database!

A relationship is a link between two related tables in a database that defines how the data is structured. Mostly, they represent real-world scenarios.

This is what we can use when learning how to create a database – a real-world scenario. Think of this sentence:

“An A has many B’s”

What this means, is that one item, “A”, has many of the other item, “B”. Most tables in your database will be related this way. This is called a one-to-many relationship.

Some examples of this are:

• A mother has many children
• A team has many players
• A company has many employees
• A book store has many books

It is almost always a one-way relationship. “A book has many bookstores” doesn’t make a lot of sense in this scenario. Sometimes they do make sense, such as “a player has many teams”, but representing that is another topic.

To structure your database correctly, this is the most important part. You need to create sentences that define these relationships between each of your tables. If you’re not sure about the relationship, you can use the words “has one” instead of “has many”, to go the other way.

For the books example, I would use:

• A book has MANY sales
• A customer has MANY sales
• A sale has ONE book (in this example, we are assuming it has one book, but yes it can have many)
• A sale has ONE customer

There should be one less of the “has many” statements as tables. I have three tables so I have two “has many” statements.

Action: Write sentences that define the ONE and MANY relationships of your tables.

If you want to watch an explanation of this concept and some examples, check out my YouTube video:

5 – Decide On A Unique Identifier

Each table in a database needs to have a unique identifier. This is a column that can be used to identify this row and only this row. This is often called a primary key. It’s done as part of the normalisation process of a database.

The simplest way to do this is to add an attribute to each table to identify it (we could use concepts called “surrogate keys” or “composite keys”, but to keep it simple, let’s stick to a single new column). I like to use the name of the table with the letters ID at the end of it.

For each of our tables, we can add the following attributes.

• Books – title, author, price, book ID
• Customers – first name, last name, address, customer ID
• Sales – sale date, customer, book, price, sale ID

I’ve underlined the new fields, as this is the convention for indicating these identifying attributes. Learning how to design a database means learning these little things as well.

Action: Add attributes to each of your tables so you can identify them. Use the “tablename ID” structure if you want.

6 – Include The Identifiers In Each Table

Now, we need to go back to our relationships, and learn how to link them. They are linked using these new ID fields we created, and these ID fields need to be in both tables.

The “has many” sentences we created before can be used to structure our tables. As mentioned before, I have two of the “has many” statements and three tables.

• A book has MANY sales
• A customer has MANY sales

Alright, so how do we actually link them? Let’s start with the first point.

A book has many sales.

What does this mean? It means that for each sale record, it has one book. It means that each book can have many sales.

To identify this relationship, we need to add the ID from one table into the other. Can we add the sale ID into the books table? We can’t because this would mean we can only store one sale ID for a book, and a book has many sales.

Can we store the book ID in the sales table? Yes, and this is what we should do. Each sale has one book, and to identify which book it is, we need to use the unique identifier of that book.

So, our books and sales tables would look like this:

• Books – title, author, price, book ID
• Sales – sale date, customer, book, price, sale ID, book ID

I’ve used italics to indicate that it refers to a column in another table. We can now tell that a sale has a book ID, and by linking the book ID to the books table, we can see what the details are of that book.

Now, the next point:

A customer has MANY sales

Could we put the customer ID in the sales table? Yes.

Could we put the sales ID in the customer table? No, as it would be the same as above, we couldn’t store a customer having more than one sale.

Let’s do the same thing with these two tables now.

• Books – title, author, price, book ID
• Sales – sale date, customer, book, price, sale ID, book ID, customer ID
• Customers – first name, last name, address, customer ID

It’s OK to have two italics columns in the one table. It just means it relates to two things.

These fields in italics are called “foreign keys“. They refer to the primary key in another table.

Action: Using your relationship sentences, work out what ID fields can be placed in the other table, to link the tables together.

Final Tips On How To Design A Database

If you’ve followed the steps above on how to design a database, you should now have a design of your very own database! That wasn’t too hard, was it?

I’ve got a few final tips that I can share when learning how to design a database”

• Draw it out. It helps a lot to be able to draw the database out as you’re doing it. This could be using pen and paper, Microsoft Visio, or some other kind of drawing program. Getting a visual representation of these tables and attributes will make it easier
• Name your tables in the singular form. I know I’ve used the plural form above, but I recommend naming your tables using the singular form. It’s just a good standard to get used to using. This is because a table represents an object or entity, and each row represents one of those.
• Consider the data types. When it comes to building your table, you’ll need to decide on the type of data that each attribute needs. Is it a number, text, dollar, date, time, or something else?
• Consider the long term support and changes. Databases don’t often stay the same as they were when they were created. They change a lot. You should consider how easy it will be to make changes in the future. If you have a complicated database, it might be harder to maintain.

Well I hope these instructions are helpful and that they provide some clear steps on how to design a database. What problems or questions do you have on this process? Share your questions in the comments section below.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

When designing a database a what is an important consideration?

What are considered best practices when designing a database?

What is the most important thing in database?

What are the 3 biggest things you would consider when developing the database design for a students Records Mgmt system *?