Discussion on optimization design of database table structure in multi-layout forum_PHP tutorial

I have been thinking about a new forum recently. I would like to discuss with you the issue of "optimized design of database table structure for multi-layout forums".

Question raised:
As a comprehensive forum, it is bound to encounter the problem of forum classification. The common single-level, multi-page structure is too simple, and cannot handle classification issues, making it impossible to achieve more complex classifications. Although a multi-level and multi-page structure like phorum can achieve arbitrarily complex classification (can achieve infinite levels of directory classification structures), the problem is that navigation is not intuitive and inconvenient. Therefore, I think that the two-layer multi-page structure currently common on the Internet is relatively reasonable. It basically meets the requirements of comprehensive forum classification and can more easily implement an intuitive and convenient forum navigation interface. Examples include www.chinaasp. com current forum. This structure has two levels of directories, the upper level is the general category, and the lower level is the classification, that is, the specific forum layout. How to use database tables to implement this structure more efficiently?

Option 1:
No matter how many forums the forum has in the future, all posts will be stored in a table. This table will record all the information of a post and the ID number of the subcategory to which the post belongs. Assume that this table is called T_articles, use table T_columns1 to record the major category information (the major category name and the ID number of the major category), and use table T_columns2 to record the subcategory information (the subcategory name, the subcategory ID number and the major category to which the subcategory belongs). ID number)

Option 2:
Create a new table for each subcategory, and store all posts belonging to this category into this table. This table only records the self-information of a post (topic, text, etc.), such tables are represented by T_articles1, T_articles2, etc. Use table T_columns1 to record major category information (major category name and ID number of the major category), and use table T_columns2 to record subcategory information (subcategory name, subcategory ID number, and the major category ID number to which the subcategory belongs)

Requirements:
1. It is required that both large and small categories can be easily expanded in the future;
2. It is required that the designed table structure can improve the efficiency of database retrieval;

Comparison:
Scheme Which one is better, plan one or plan two?

Would you like to discuss it?


Reprinted a reply from a friend in "Jiangnan Forum"
cach posted on Programming Basecamp at 2001-1-18 23:51:59
Option 1 is better to expand the layout. But the disadvantage is that as the number of posts increases, the amount of data will become larger and larger.

It will be more time-consuming to do a query, and it will be troublesome if there is a problem with the data table.
But the advantage is that programming is much easier. Many forums now use this solution!
Option 2 will be faster in comparison, but it may take more time to program!
I think some content should be added to the second option, that is, adding a field to T_columns2 to
put the table name of the newly created subclass (because the second option is to build a table in a discussion area, these tables Because they are all added dynamically by
, it is necessary to add a field to store the table name. Then you can use this field as a parameter
to generate the corresponding discussion area). The advantage is that it is fast and has one discussion area table. If something goes wrong, it won't affect
other discussion forums, so I still think option two is better!
The above is my personal opinion!

Copyright statement: The copyright of this post belongs to the submitter, cach. Reprinting or reposting on other websites must indicate the source. Reprinting in traditional media must contact the original author and the Jiangxi hotline in advance. The submitter of this post speaks purely These are personal opinions and have nothing to do with the position of this website.