Home > Article > Backend Development > BBS design based on mysql (2)_PHP tutorial
BBS design based on mysql (2)_PHP tutorial
- WBOYOriginal
- 2016-07-21 16:03:27943browse
3. Database design
The key is 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 of the table is designed to use 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: essence area index table, ilinux, ilinux01, icampus, icampus04
j: essence area article table, jlinux, jcampus,
In addition, should we 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 name
userlevel longint, // 64 kinds of 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 other data? Do I need to set aside a certain reserved value so that I can alter table in the future?
// 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],// Chinese description of the category: Computer World
classtable char[20], // The page table corresponding to the special category
// Generally speaking, each page only belongs to one category. For special categories, such as the fist section,
// New layout can be described by a special table
Layout table: sboard
boardnum int unique, // Identification of the layout (is it necessary?)
boardid char[20], // English name of the board
boardname varchar[100], // Chinese name of the board
boardclass char[20], // Classification of the board
boardsysop varchar[100], // Bamboo list
boardposts int, // The number of articles in the board
boardlevel int, // The read and write permissions of the board
indextable char[20], // The name of the index table corresponding to the board: 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 should
// allow greater flexibility? In addition, can the capitalization issue of the layout be directly defaulted to
// Only the first letter is capitalized,
Special category 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.
// Do the unread articles in the user page have a lot of unread data? Should we build a bunch of tables
// to achieve this?
// The voting function is not considered yet. . . .