Do you want to create the next StackOverflow?
One part of that is designing the database, and in this video, I’ll show you how.
We’ll start with a list of 7 different requirements for creating
a question-and-answer site like StackOverflow.
We’ll go through each of the requirements,
and start creating or making updates to the database design.
At the end, we should have a design that meets the requirements and can be used for
a StackOverflow-style question-and-answer site.
I’ll be using a tool called LucidChart for the diagram,
but you can use any other tool you prefer, or pen and paper.
Let’s look at the first requirement.
The first requirement is that people can register on the site as a user.
This refers to the Sign Up process that you can do on the StackOverflow website.
You can enter some details and create an account.
We’ll also assume you can enter some details about your user profile,
such as your location and a bit about yourself.
So how do we add this to our diagram?
We start by adding a table to our diagram. We’ll call it a user table.
We’ll add a field called id,
which will be the primary key for the table for uniquely identifying the user.
Next, we’ll add in a few other fields that the user would enter when signing up,
which are the display name, their email address, and password.
We’ll also add an about me field and a location field that the user can capture on their profile.
Finally, we’ll add a created_date field, so we can see when the user
record was created. This isn’t essential for our design, but it can be helpful.
So we’ve got a table for our users that can store information when someone registers for the site.
Let’s go to the next requirement.
The second requirement is that users can create posts, which are treated as questions on the site.
This is represented as a question on the page here,
where someone creates a post asking a question, and other people can answer it.
We can represent this on our diagram by creating a new table, which we will call post.
We’ll add an id column as the primary key, which you’ll notice I do for most of my tables.
We’ll also add a post title, which is the name or title of the post. The next
field to add is the post details, which is the body of the post or the question being asked.
We can also add a created date column so we know when the post was created.
How do we record who created the post? We do this by adding a link to the user table.
We’ll add a new field, and call it created by user id.
This is a foreign key because it links back to the primary key of the user table.
That’s our post table to meet the second requirement. What’s next?
The third requirement is that users can write answers to posts.
On the StackOverflow site, this is what an answer looks like.
A user can add one or more answers to a post from another user.
It looks similar to a question, but it’s in response to another question.
How do we add this to our diagram?
If we think about an answer, we need to capture the user who wrote it,
the body of the answer, and when it was created. This is very similar to a post.
There are two ways we could do this.
One way is to use the same post table to store answers,
and have a field that indicates whether this is a question or an answer.
This could be a separate post_type table that has two records: one for question and one for answer.
We would then have a link to the question record that this answer is in response to.
The table design could look like this.
However, we could instead have a separate table for answers. It would have similar fields to the
post table, but we won’t need a post title. We have the same field to link to the created user,
and a field to link to the question that the answer is in response to.
This would be the design I would prefer. It’s simpler as it has one less table,
and avoids the self join.
However, if you have different types of posts,
or answers in response to other answers, you could have a single table.
Let’s go to the next requirement.
Requirement number 4 allows users to add tags to a post.
On the StackOverflow website, here’s how this looks.
A tag is an indicator or label that is added to a post to indicate what
it’s about. There’s a defined list of tags, and users can add one or more to the post.
Let’s see how to add this to our diagram.
We’ll create a table to store the list of tags, which we will call tag.
In this table we will have an id.
We’ll also have a tag name which is a short name of the tag and will be displayed on the page.
We can also have a description, which can
be used to provide a definition of the tag or more information.
How can we relate that to posts?
A post can have many tags, but a tag can be used on many posts. This means this is a many-to-many
relationship. To represent this on our diagram, we need to create a joining table between tags
and posts, to store the combinations of tags and posts that are used. I’ve got another video
here on many-to-many relationships if you want to know more about why this is done.
So, in this joining table, we add both the post ID and the tag ID.
This design can now handle the ability to add tags to posts.
Requirement 5 says that users can add comments to questions or answers.
On the StackOverflow website, we can see the comments here.
They are a shorter text input, not quite a full post or answer.
How can we add this to our diagram?
Because they are more limited than an answer or a post, we can create a separate table for them.
We’ll create a table called comment. We’ll add in an id field, a comment
text field to store the text of the comment, the created date, and the user who created it.
How do we record what the comment is related to?
It could be for a post, so let’s add a post ID.
But it could also be for an answer, so let’s add an answer ID.
We’ve got two foreign keys here, and a comment can only exist for one of them.
There’s a possibility with this design that both of the fields could be populated,
which means a comment would be related to both a post and an answer. This is not ideal.
How can we resolve this?
We could have a combined field called post_or_answer_id,
but then we can’t use it as a foreign key as it would relate to either one of these tables.
What else can we do?
Maybe the better design is the alternative design we had earlier,
which had both questions and answers inside a single posts table.
Here’s that design. We have a single post table, and the related post_type table.
We can add the comment table, with the same fields.
We have one field that links to the post, rather than a post Id and an answer ID field.
This is a cleaner design, I think, and avoids the issues we saw in the earlier design.
What’s the next requirement?
Requirement 6 is about allowing users to upvote or downvote questions and answers.
This is done on the StackOverflow site by a user clicking either the
up arrow or the down arrow next to the question or the answer.
We can capture this in our design by adding a
new table called vote to capture the votes that a user has made.
This would contain a primary key of id, a post ID for the post that is being voted on,
the user ID that made the vote, and the created date.
We also want to capture whether the vote was an up vote or a down vote.
We could have this as a vote type with a check constraint on it to store the two possible values,
but I’ll create a separate table called vote type to store the different options,
and link it back to the main vote table. This is just a personal preference.
So here’s our design, and it meets requirement number
6 which is about votes for questions and answers.
The last requirement is for a user to be able to mark an answer as an accepted answer.
On StackOverflow, this means that the user who asked the question can look at all of the
answers they have received, and choose which one is the answer they want to use for the question.
We can represent this in our diagram.
In our posts table, we want to store which answer is the accepted answer for the question.
One way to do this would be to add a new field called is_accepted,
which would store true if it is the accepted answer.
But I don’t think we could easily ensure that there is only one accepted answer for a question.
Another way to do this, and I think this is better,
is to have a link to the answer ID for the accepted answer.
This means we add a new column to the post table called accepted_answer_id.
This would relate to the primary key of the post table.
For a question that has an accepted answer, we would look at that ID to find the answer.
If a question does not have an accepted answer, this field would be null.
So, that’s how we could achieve that requirement.
Here’s our final database design. I’ve added a link to this design in the description.
We can see the post table is the centre of the design and links to many other tables.
We have a user table for user information, a vote table to store votes for posts, a table to store
comments on posts, and several other lookup tables such as vote type, post type, and tag.
Once again, this isn’t the real StackOverflow database design. This is just one way you
could design the database if you wanted to build a website like StackOverflow.
There are a few StackOverflow features that we didn’t include in this design.
You could think of them as enhancements to this if you want to practice your database design.
We didn’t cover the ability to moderate posts, such as flagging them or removing them.
We didn’t include the ability for users to earn badges.
We didn’t have the ability to close a question and the reason to close it,
such as it being a duplicate of another question.
We don’t have any history of posts such as what has changed over time.
We don’t have the ability to link to related posts directly.
Finally, we don’t have the ability to suggest edits to posts.
So what’s next? If you want to convert this design into an SQL script that can actually
create the SQL database, you can follow the process I describe in my other video here.
Let me know in the comments below what you think and if you have any questions on the design.
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.