3. Database Design
The key is the efficiency of mysql, and the rational allocation of mysql memory, especially the table cache
size. Also, what if the system suddenly loses power? Is mysql robust?
The name design of table uses a one-bit prefix to indicate the type, and all are expressed in lowercase (?), for example:
The system's database is led by s, such as user table: suser (what is sUSER?), which is as follows:
s: system table, suser, slass
m: User letter table, msysop, mdrangon
w: user message table, wsysop, wdrangon
a: layout index table, alinux, acampus
b: Layout article list, blinux, bcampus
c: Special classification layout table, cnewboard
i: Essential area index table, ilinux, ilinux01, icampus, icampus04
j: Featured article list, jlinux, jcampus,
In addition, should we use strings or numbers as identification? For example, an account called sysop,
Is the id 1, is the table of his letter msysop or m00001? Similarly, a version called campus, corresponding to
If the code is 5, is the table name of this version of the article bcampus or b00005? It might be easier to use strings
Understand, check it wrong.
User information table: suser
usernum int unique, // A unique identifier, up to 30,000 accounts, is it too few?
userid char[20] primary key, // Sort keywords, id, all lowercase.
passwd char[20], // Password, store the encrypted ciphertext.
real id char[20], // century id, case mix.
username char[24], // User's mud 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? Whether to leave a certain reserved value, then alter table will be
// How efficient is it when adding new fields?
Layout classification table: slass
classnum int unique, // Classification identification
classid char[20], // English id of the classification: computer
classname varchar[100],// Chinese description of the classification: Computer world
classstable char[20],// Layout table corresponding to special categories
// Generally speaking, each layout belongs to only one category. For special categories, such as the Riot section,
// The new page can be described in a special table
Layout table: sboard
boardnum int unique, // The layout logo (required?)
boardid char[20], // English name of the page
boardname varchar[100], // Chinese name of the page
boardclass char[20], // Classification of the layout
boardsysop varchar[100], // List of bamboos
boardposts int, // Number of articles on the page
boardlevel int, // Read and write permissions of the layout
indextable char[20], // The name of the corresponding index table for the version: aboardid?
texttable char[20], // The corresponding article table name for the page: bboardid?
// Is the last two items necessary to appear, whether they can be used as inevitable correspondence, or are they allowed
// More flexibility emerges? In addition, can the upper and lower case problems of the layout be directly defaulted
// Only capital letters at the beginning,
Special classification layout table: snowboard, sstarboard
boardid char[20], // id of the layout
// Is such a table necessary?
Layout index table: campus, linux, football. . . . . .
id int, // The article number needs to be adjusted manually? ? ? ?
mark char[1], // Article mark, m, g, b, d. . . .
title varchar[100], // Article title
writer char[20], // Article author id
posttime time, // Post time
textnum longint, // Corresponding number? ? ? No adjustment
Page article list
textnum longint, // Article number?
textword text, // Article content?
// Is it necessary to separate the index and the content of the article? From the perspective of efficiency, and lazy flush
// It is inevitable. Delete it first.
// Whether the unread data of the page articles in the user is relatively complicated, and should a bunch of tables be created
// What can be achieved?
// The voting function is not considered yet. . . .
The key is the efficiency of mysql, and the rational allocation of mysql memory, especially the table cache
size. Also, what if the system suddenly loses power? Is mysql robust?
The name design of table uses a one-bit prefix to indicate the type, and all are expressed in lowercase (?), for example:
The system's database is led by s, such as user table: suser (what is sUSER?), which is as follows:
s: system table, suser, slass
m: User letter table, msysop, mdrangon
w: user message table, wsysop, wdrangon
a: layout index table, alinux, acampus
b: Layout article list, blinux, bcampus
c: Special classification layout table, cnewboard
i: Essential area index table, ilinux, ilinux01, icampus, icampus04
j: Featured article list, jlinux, jcampus,
In addition, should we use strings or numbers as identification? For example, an account called sysop,
Is the id 1, is the table of his letter msysop or m00001? Similarly, a version called campus, corresponding to
If the code is 5, is the table name of this version of the article bcampus or b00005? It might be easier to use strings
Understand, check it wrong.
User information table: suser
usernum int unique, // A unique identifier, up to 30,000 accounts, is it too few?
userid char[20] primary key, // Sort keywords, id, all lowercase.
passwd char[20], // Password, store the encrypted ciphertext.
real id char[20], // century id, case mix.
username char[24], // User's mud 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? Whether to leave a certain reserved value, then alter table will be
// How efficient is it when adding new fields?
Layout classification table: slass
classnum int unique, // Classification identification
classid char[20], // English id of the classification: computer
classname varchar[100],// Chinese description of the classification: Computer world
classstable char[20],// Layout table corresponding to special categories
// Generally speaking, each layout belongs to only one category. For special categories, such as the Riot section,
// The new page can be described in a special table
Layout table: sboard
boardnum int unique, // The layout logo (required?)
boardid char[20], // English name of the page
boardname varchar[100], // Chinese name of the page
boardclass char[20], // Classification of the layout
boardsysop varchar[100], // List of bamboos
boardposts int, // Number of articles on the page
boardlevel int, // Read and write permissions of the layout
indextable char[20], // The name of the corresponding index table for the version: aboardid?
texttable char[20], // The corresponding article table name for the page: bboardid?
// Is the last two items necessary to appear, whether they can be used as inevitable correspondence, or are they allowed
// More flexibility emerges? In addition, can the upper and lower case problems of the layout be directly defaulted
// Only capital letters at the beginning,
Special classification layout table: snowboard, sstarboard
boardid char[20], // id of the layout
// Is such a table necessary?
Layout index table: campus, linux, football. . . . . .
id int, // The article number needs to be adjusted manually? ? ? ?
mark char[1], // Article mark, m, g, b, d. . . .
title varchar[100], // Article title
writer char[20], // Article author id
posttime time, // Post time
textnum longint, // Corresponding number? ? ? No adjustment
Page article list
textnum longint, // Article number?
textword text, // Article content?
// Is it necessary to separate the index and the content of the article? From the perspective of efficiency, and lazy flush
// It is inevitable. Delete it first.
// Whether the unread data of the page articles in the user is relatively complicated, and should a bunch of tables be created
// What can be achieved?
// The voting function is not considered yet. . . .