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.
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!
I found this to be incredibly helpful and very clearly presented. I am creating databases for our lab, and this helped me find simple ways to explain to my co-workers what needs to be done. The sentence to write what you are trying to represent, and identify the object was useful for all of us to describe how we want to store the data and understand the relationships. Thank you!
I am a student. I am preparing for the exams and I found this very helpful. Thank you!
Hi, if my Club Membership database was an annual record, then any change(s) to the Membership fee(s) would be reflected in an updated MEMFEE table.
How would I instead cater for annual change(s) in Membership Fee, so I could eventually run a report to show a member’s payments while they have been a member?
Having a date of payment column in your MEMFEE table would cater for the annual change in membership fee. If you are running a report to see member’s payment over the years you can PIVOT to see the average or cumulative fee paid by members or a member.
Thank you databaseStar , very clear to understand and practice easily
Are there some more examples of database design, and techniques for Modeling particular scenario’s, please let me know,
Concepts well articulated here and in other posts. Thank you.
I called in to grab the cheat sheets offered, then was lucky enough to fall on the post about ‘Database Design’, which I found really helpful, and am going to put into practise right now. I am trying to develop a simple but useful tracker for exercise and results to better understand my training and develop skills.
Thanks
What an excellent post. The information you created step by step is just what I needed to help create a picture for my data base class this week. Thank you!