bbs.txtnovel.com BBS design based on mysql (2)

3. Database design
The key is still the efficiency of mysql, reasonable allocation of mysql memory, especially the
size of the table cache. In addition, what happens when the system suddenly loses power? Is mysql robust?
The name design of the table uses a prefix to indicate the type, all expressed in lowercase (?), for example:
The database of the system is preceded by s, such as the user table: suser (where is sUSER?), as follows:
s: System table, suser, sclass
m: user mail table, msysop, mdrangon
w: user message table, wsysop, wdrangon
a: layout index table, alinux, acampus
b: layout article table, blinux, bcampus
c: special Classification layout table, cnewboard
i: Essential area index table, ilinux, ilinux01, icampus, icampus04
j: Essential area article table, jlinux, jcampus,
In addition, should I use strings or numbers as identifiers? For example, for an account named sysop, its
id is 1. Is the table of his letter msysop or m00001? Similarly, for a version called campus, the corresponding
code is 5. So, is the table name of the article in this version bcampus or b00005? Maybe using strings will be easier to understand. Let’s check the error.
User information table: suser
usernum int unique, // Unique identifier, up to 30,000 accounts, is it too few?
userid char[20] primary key, // Sorting key, id, all lowercase.
passwd char[20], // Password, stores the encrypted ciphertext.
realid char[20], //actual id, mixed case.
username char[24], // User’s nickname
userlevel longint, // 64 permissions?
numlogins int,
numposts int,
firstlogin time,
lastlogin time,
staytime time, /* total stay time*/
lasthost char[32],
email varchar[100],
address varchar[100],
/ / Do you need any other data? Do I need to set aside a certain reserved value so that I can alter table later
// How efficient is it when adding new fields?
Layout classification table: sclass
classnum int unique, // Classification identifier
classid char[20], // Classification English id: computer
classname varchar[100], // Classification Chinese description: Computer World
classtable char[ 20], // The layout table corresponding to the special category
// Generally speaking, each layout only belongs to one category. For special categories, such as the fist section,
// New layouts can be described with special tables
Layout table ：sboard
boardnum int unique, // Identification of the board (need it?)
boardid char[20], // English name of the board
boardname varchar[100], // Chinese name of the board
boardclass char[20], // The category to which the board belongs
boardsysop varchar[100], // List of posters
boardposts int, // The number of articles in the board
boardlevel int, // The read and write permissions of the board
indextable char[20], // The index corresponding to the board Table name: aboardid?
texttable char[20], //The article table name corresponding to the layout: bboardid?
// Is it necessary for the last two items to appear? Can they be regarded as inevitable correspondences, or does it allow
// greater flexibility? In addition, can the capitalization issue of the layout be directly defaulted?
// Only the first letter is capitalized,
Special classification layout table: snewboard, sstarboard
boardid char[20], // The id of the layout
// Is such a table necessary?
Layout index table: acampus, alinux, afootball. . . . . .
id int, // Article number, do you want to adjust it manually? ? ? ?
mark char[1], // Article mark, m, g, b, d. . . .
title varchar[100], // Article title
writer char[20], // Article author id
posttime time, // Publication time
textnum longint, // Corresponding number? ? ? No adjustment
Layout article table
textnum longint, // Article number?
textword text, // Article content?
// Is it necessary to separate the index and article content? From an efficiency point of view, lazy flush
// is inevitable. To delete, mark it first.
// Are the unread data in the user's page articles relatively complex? Should we build a bunch of tables
// to achieve this?
// The voting function is not considered yet. . . .

The above introduces the bbs design of bbs.txtnovel.com based on mysql (2), including the content of bbs.txtnovel.com. I hope it will be helpful to friends who are interested in PHP tutorials.