Database Design for the MiniBBS series (MVC) by yourself

 

Write BBS on your own as an exercise for learning jsp basics for more than a month. It is a complete Jsp website.

This article introduces the database design part of this simple BBS.

The simplest BBS system requires several entities: Forum, topic (post), topic reply, and user.

The main logical relationship is

1. The post belongs to the Forum

2. Reply to a post

3. Post and reply to the user

4. the user manages Forum, post, and reply (by permission)

5. Users Manage Users (by permission)

 

Therefore, the design of basic database E-R diagram is as follows

 

 

Finally, determine the number of tables

User table

Post table post

Reply table sub_post

Block

Post reply relationship table

Forum post relationship table

Forum user relationship table

 

Modify

In actual design, the last_mod_time attribute added to the table post is modified to indicate the last modification time of the post, so as to determine the order of the post according to the modification time from nearly to a long time.

You can also query the reply time of the last reply record to determine the sorting. However, considering that the query is complex and time-consuming, the cost-effectiveness is not high, you can add an attribute.

 

Discussion

Some discussions about Database Design:

Is database design best to satisfy the database paradigm?

I found that when the relationship between database entities is or, it is best not to satisfy the Three-paradigm database design scheme. On the contrary, this design may also generate redundant data.

For example, the database in the system

Post table (post id, post information)

Reply table (reply id, reply Information)

Post reply relation table (post id, reply id)

 

The relationship between the post and the reply is 1: N. At this time, if we change the reply table to (reply id, reply information, post id ), we can consider that there are some functional dependencies between the reply information and the reply id, that is, such a design does not meet 2NF. That is to say, we will combine the two relationships (replies and replies) that should be separated, but this merger reduces some redundancy and will not cause deletion or insertion exceptions. The query is simplified.

Therefore, this design method can be considered when there is such a 1: N or 1:1 relationship in the database. Although it does not necessarily meet the database design paradigm, it is a more rational design method.

This article is from the "walking all the way" blog