The code of this project has been open source. See:

Front-end engineering: vuE3 – TS -blog-frontend

Back-end engineering: Express-blog -backend

Database initialization script: pay attention to the public number front si Nan, reply keyword “blog database script”, can be obtained.

What function should a blog system have, I believe we are very familiar with, its core is nothing more than articles, classification, creation. And functions like tags, comments, messages, exchanges, background management, are icing on the cake.

To achieve these functions, the most important thing is to sort out the relationship between each function. When it comes to relationships, relational databases naturally come to mind.

Before designing a database, it is necessary to clarify the relationship between entities. Here, a modeling language such as E-R diagrams or UML will be used to make a brief design.

But from my perspective as a non-professional database user, I think you can go beyond the formal, you don’t have to be limited to E-R diagrams or UML, you can also use a graphical representation tool like a mind map. Because this is just a schematic design phase.

As shown above, FOR my personal blog, I did a simple entity and entity relationship design.

Many-to-many relationships

Among them, the article table is the core. Considering that an article may be associated with multiple categories or tags, and there may be multiple articles under one category or tag, I design many-to-many relationships here and use the relationship table.

Relational tables do not involve many fields.

It is primarily the two foreign keys designed in the relational table that play a key role.

Based on such a relationship table, many-to-many relationships can be completed.

One-to-many relationship

There are comments under an article. An article can have multiple comments. The relationship between article and comment is one-to-many, which is quite understandable.

For this one-to-many relationship, my design is to implement the association with a foreign key article_id in the comment table.

To query the comments under an article, the corresponding comment data can be filtered according to the condition article_id.

SELECT * FROM comments WHERE article_id = 229;
Copy the code

The child relationship

Similarly, there will be many replies under one comment. I designed the reply table separately for the replies. Comment and reply are also one-to-many, and the comment_id foreign key in the reply table is associated with the comment table.

In addition to responding to comments, you can also respond to a specific response, something like this:

This seed level relationship needs a parent_ID to record. According to the relationship of parent_ID, we can get a reply tree on the business side.

Status field

Many businesses are inseparable from state maintenance, such as logical deletion of data, public/private processing of articles, review mechanism of comments/replies, all of which require some flag bits to describe the state and provide some business interfaces to maintain the state.

summary

This article is Vue3+TS+Node to create personal blog (database design), mainly introduced me in the blog system design database some of the main ideas and concerns, the next will be for some specific business implementation to carry out a more detailed analysis, please look forward to!

series

Vue3+TS+Node to build personal blog series of articles as follows, continue to update, welcome to read! Praise attention do not get lost! 😍

  • Vue3+TS+Node to create personal blog (overview)
  • Vue3+TS+Node to create personal blog (database design)
  • Vue3+TS+Node to create personal blog (back-end architecture)
  • Vue3+TS+Node to create personal blog (front-end architecture)
  • Vue3+TS+Node to create a personal blog (pagination model and scroll loading)
  • Vue3+TS+Node to create a personal blog (click to top and side catapult)
  • Vue3+TS+Node to create personal blog (article creation and Markdown rendering)
  • Vue3+TS+Node to create personal blog (clever design of comment system)
  • Vue3+TS+Node to create personal blog (socket. IO online chat room)
  • Vue3+TS+Node to create personal blog (login, permissions, background management)
  • Vue3+TS+Node to create a personal blog (automated deployment)
  • Vue3+TS+Node to create personal blog (small program blog)