Design of BBS based on MySQL (II.)

3. Database design
The key or MySQL efficiency problem, reasonable allocation of MySQL memory, especially the table cache
Size. In addition, when the system suddenly lost power? is MySQL robust?
The name of the table is designed with a prefix indicating the type, all in lowercase (?). ), for example:
System's database, with s leading, such as user table: Suser (suser?) ), as follows:
S: System table, Suser,sclass
M: User Letter form, Msysop,mdrangon
W: User Message table, Wsysop,wdrangon
A: Page index table, Alinux,acampus
B: Layout Article table, Blinux,bcampus
C: Special Classification layout table, Cnewboard
I: the elite Area Index table, Ilinux,ilinux01,icampus,icampus04
J: The essence of the article table, Jlinux,jcampus,

In addition, is the use of strings or numbers as a sign? For example, an account called Sysop, whose
ID is 1, is his letter msysop or m00001? Similarly, a version called campus, corresponding to the
Code is 5, then this version of the article's table name is Bcampus or b00005? It may be easier to use a string
Understand, check it wrong.

User Information table: suser
usernum int unique,//Unique identifier, up to 30,000 accounts, will it be too little?
UserID CHAR[20] PRIMARY key,//sort of keyword, id, all lowercase.
passwd char[20],//password, store encrypted ciphertext.
RealID char[20],//actual ID, mixed case.
Username char[24],//user's mud claim
Userlevel Longint,//64 permissions?
Numlogins int,
numposts int,
Firstlogin time,
Lastlogin time,
Staytime time,/* Total stay * *
Lasthost Char[32],
Email varchar[100],
Address varchar[100],
Do you need any more data? Whether you need to set aside a certain amount of reserved value, later ALTER TABLE to
What is the efficiency of adding new fields?

Page Classification table: Sclass
classnum int unique,//category identification
ClassID char[20],//classification of English Id:computer
ClassName varchar[100],//Classification of Chinese description: Computer World
Classtable char[20],//special categories corresponding to the layout table
In general, each layout belongs to only one category, for special categories, such as fist plates,
New layout, you can use a special table to describe

Layout table: Sboard
boardnum int unique,//Layout logo (required?) ）
Boardid char[20],//The English name of the page
Boardname varchar[100],//The Chinese name of the page
Boardclass char[20],//layout-owned categories
Boardsysop varchar[100],//List of moderators
boardposts int,//page number of articles
Boardlevel int,//page read/write access
Indextable char[20],//layout corresponding to the name of the index table: Aboardid?
Texttable char[20],//layout corresponding to the article table name: Bboardid?
The last two items are not necessary, whether they can be an inevitable counterpart, or allow
Greater flexibility? Whether the case of another layout can be directly default
Capitalize only the initial letter,

Special classification table: Snewboard, Sstarboard
Boardid char[20],//Layout ID
Is such a watch necessary?

Layout Index Table: Acampus,alinux,afootball ...
ID int,//article ordinal, to manually adjust????
Mark Char[1],//article Mark, m,g,b,d ....
Title varchar[100],//article titles
Writer Char[20],//article author ID
Posttime time,//Published
Textnum longint,//corresponding number??? Do not adjust

Layout Article table
Textnum Longint,//article number?
Textword text,//article content?
Is it necessary to separate the index from the content of the article? In terms of efficiency, moreover lazy flush
is inevitable. Delete is also to make a mark first.

The user's layout article is not read the data is more complex, whether should build a bunch of tables
To achieve it?
Voting function is temporarily not considered ....