Have you got an idea for a database but don’t know how to design the database?
You’re in the right place.
In this video, I’ll show you the step-by-step process you can take to turn an idea or set
of requirements into a database design.
Welcome to the Database Star YouTube channel - the place for developers looking to improve
their database and SQL skills.
When we need to create a database, we need to work out how the data is going to be stored.
This means we need to work out what tables are needed, what attributes they have, and
whether the attributes are in the right place.
If you’ve got an idea, it can be hard to get from this idea to a well-designed database.
I’ll show you the steps you can take using an example in this video.
Let’s get started.
The first step is to write down your idea or your requirements.
Write down a sentence or two that describes the kind of data you want to store, or the
kind of things you want your application to do.
For example, if you’re designing a social networking app, you may write something like
“allow users to upload photos, comment on photos, tag other users, like photos, and
post text updates”.
If you’re designing a website for a school, you may write something like “allow students
to select classes to enrol in, allow teachers to enter timetables for classes, and allow
teachers to record attendance for classes”.
Just a sentence or two will do.
This will help us in the upcoming steps.
For the rest of this video, we’ll imagine we’re creating a database that tracks doctor
appointments.
We can describe this as **“allow patients to book a doctor appointment on a specified
date and time with a doctor, record when the patient arrives at the clinic, and record
when the patient has completed their appointment with the doctor”.**
Step 2 is to identify the nouns in the sentences that you wrote.
A noun is an object or a thing.
Using our doctor appointment example, here was the description we wrote.
As we read this, we can identify the nouns.
The nouns are patient, doctor appointment, clinic, and doctor.
Read through the description you wrote for your idea and identify the nouns.
What do we do with these nouns?
These are used to create our tables.
We start here with a blank screen.
I’m using LucidChart to create this diagram, but you can use any other diagramming tool,
or even pen and paper.
Check out the links in the description for this tool, as well as a page that lists other
tools you can use.
Let’s add our tables.
We’ll add a table for patient, one for appointment, one for clinic, and one for doctor.
We don’t need to worry about the attributes just yet, or giving the tables better names.
This will do for now.
In this LucidChart tool, a set of template fields are added here.
We can ignore those for now.
Let’s look at the next step.
Step 3 is to add attributes to our database design.
We can get these from a combination of the description we wrote, our understanding of
the idea, and some assumptions.
We don’t need to know all of the attributes to get started.
We can come back and make changes as we learn more, which is common if you’re working
for a company as you’ll likely need to check with other people.
So, here’s our description again.
We want to allow patients to book an appointment on a specified time with a doctor.
What does this mean?
It could mean that an appointment has a date and a time.
It could also have a doctor.
And, of course, the patient.
Let’s add these to our diagram.
We’ll add a few new fields to the appointment table: appointment date, appointment time,
doctor, and patient.
What else can we do?
We want to record when a patient arrives at a clinic.
They would be arriving for an appointment.
We could store this in a couple of ways.
We could create an arrivals table to store a record of patients and when they arrive.
Another way would be to store it on the appointment table.
A patient is arriving for an appointment, so we could say that their arrival time is
related to the appointment.
Let’s add a new field to this appointment table for the arrival time.
What’s next?
We want to record when the patient has completed their appointment with the doctor.
Back on our diagram, this could be done in a similar way to the arrival time.
We could record a completed time for our appointment in the appointment table.
We can see we have a lot of information in the appointment table, but nothing in the
other tables.
How can we determine if we need to store anything for these tables?
If we’re working for a company, we could ask those who know the subject area.
If you’re creating an app, you may have an idea yourself.
Or we can make some assumptions.
This is what we will do now.
For the patient, what kind of information would we need to record?
Probably their name, which we could store as a firstname and lastname.
This isn’t always ideal, as names can be complicated, and I’ll have another video
out soon on ways to store names in a database.
We may want to store their date of birth, which can be done as another field.
There may be a whole range of other information we could store, such as email or phone number
or address.
We’ll leave those out for now.
How about the clinic?
We don’t know much about the clinic, but perhaps the clinic has a name and an address,
so let’s add those.
How about the doctor?
We could add a firstname and lastname for the doctor as well.
If we think about booking appointments, we may want to indicate availability for doctors
and only allow patients to book when doctors are available, or on days that they work.
We didn’t specify that in our description, so it’s up to us whether we want to add
it in.
We’ll leave it out for now, just to keep it simple, but you can add it in if you’re
following along.
Now we’ve added a few more attributes to our tables.
Let’s see what’s next.
Step 4 is to add relationships to our design.
This allows for data to be stored in one place, and allows for many other advantages of relational
databases.
In our design, we’ve got four tables, but they are not related.
Let’s start with our appointment table.
In this table, we have some information about the appointment.
We also have a patient field.
Rather than a field called patient, it’s better to have a link to a record in the patient
table.
We do this by adding an ID into the patient table to identify the record, and having the
same ID in the appointment table.
The columns don’t need to have the same name, so it’s up to you what you call them.
Here, we’ll call the column in the patient table ID, and in the appointment table we’ll
call it patient_id.
In LucidChart, we can use the first section here on each table to define the keys.
This means we can enter PK for Primary Key, which is the unique identifier for the record.
We can enter FK for Foreign Key, which is used to link to the primary key in another
table.
We can also draw lines from the primary key to the matching foreign key, so we can see
how the tables are related.
How about the doctor?
We should take the same approach - relate it to the doctor record instead of storing
doctor information in the appointment table.
We can add an ID to the doctor table as well as the appointment table.
So, we can see that the patient table is related to an appointment, and a doctor is related
to an appointment.
What about the clinic?
An appointment is made at a clinic.
So, we can add a clinic ID to the clinic table, and a clinic ID to the appointment table.
Let’s go to the next step.
Step 5 is to assess the tables and fields and make adjustments.
We can look at our design and see if we think any adjustments need to be made.
Are there any relationships that could be missing?
Perhaps a patient has a preferred doctor, so we could link a patient to the doctor table.
Or a doctor works at a clinic, so we could link those two tables together.
We can refer back to our description and compare that to our design.
Is our design capable of capturing the data that we want?
Our description said we needed to allow patients to book a doctor appointment on a specified
date and time with a doctor.
They can do this with our design.
It also said it needs to record when a patient arrives at the clinic.
This can also be done.
They also want to record when the patient has completed their appointment.
Yes, this can be done too.
We have primary keys or IDs for most of our tables, except for the appointment table.
It’s helpful to have a primary key for all tables, so let’s add an ID to this table
as well.
You can call it ID or call it appointment ID, it’s up to you.
So, it seems like our design is sufficient for our idea.
You may want to start inserting some data into the tables to see if there are any issues
with how the tables are stored.
You may find some scenarios that the design does not cover.
If so, that’s OK, as you can adjust the design.
This process I demonstrated is a brief explanation of the process of normalisation, which is
a standard way to build an efficient and well-designed database.
If you want to go more in-depth with designing a database, including things to look out for,
more about relationships between tables, and more, then check out my Relational Database
Design course on Database Star Academy, the online training platform for SQL and database
development.
You can find out more such as what’s included by visiting databasestar.com/dsa, or clicking
the link in the description.
So, what’s next?
You’ve got a design from your idea.
The next steps are to write some Create Table statements to create the tables and some Insert
statements to insert data.
I’ve got a video here on how to do that.
You may also need to install and set up a database, such as MySQL or SQL Server, first.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.