This lecture begins the design of a multi-table blog database. It’s time for our most ambitious project yet. We are going to design a database to power a simple blog. This is a classic database design problem and it will require us to use everything we’ve learned about primary keys, foreign keys, and relationships.
In this lecture, we will focus only on the design. We won’t write any SQL yet. Planning your database structure is the most important step.
Step 1: Identifying the Core Things (Entities)
First, what are the main “things” or concepts in a blog? Let’s list them out:
- Authors: The people who write the posts.
- Posts: The articles themselves.
- Comments: The feedback readers leave on the posts.
These three things will become the main tables in our database.
Step 2: Defining the Attributes for Each Entity
Now, what information do we need to store for each of these things?
Authors Table
- A unique ID for each author. (Primary Key)
- The author’s name.
- The author’s email address.
Posts Table
- A unique ID for each post. (Primary Key)
- The title of the post.
- The full content of the post.
- The date the post was published.
- A way to know who wrote the post. (This will be a Foreign Key)
Comments Table
- A unique ID for each comment. (Primary Key)
- The name of the person who left the comment.
- The content of the comment.
- The date the comment was left.
- A way to know which post the comment belongs to. (This will be a Foreign Key)
Step 3: Defining the Relationships
Now we need to figure out how our tables connect to each other.
- Authors and Posts: An author can write many posts. A post is written by only one author. This is a one-to-many relationship. The `Posts` table will need a foreign key (`AuthorID`) that points to the `Authors` table.
- Posts and Comments: A post can have many comments. A comment belongs to only one post. This is another one-to-many relationship. The `Comments` table will need a foreign key (`PostID`) that points to the `Posts` table.
Our Final Schema
Here is a simple diagram of our planned database structure (schema):
Authors Table
AuthorID (PK)
AuthorName
Email
| | (one) V
Posts Table
PostID (PK)
Title
Content
PublishDate
AuthorID (FK)
| | (one) V
Comments Table
CommentID (PK)
CommenterName
CommentContent
CommentDate
PostID (FK)
In the Next Lecture…
Now that we have a solid plan, we will translate this design into SQL. In the next lecture, we will write the CREATE TABLE statements for all three tables, including the primary and foreign key constraints to build our blog database.
