3. Database Design
The key is the efficiency of mysql. Allocate the mysql memory reasonably, especially the table cache
Size. What happens when the system suddenly loses power? Is mysql robust?
Table name design, with a prefix indicating the type, all in lowercase (?), For example:
The system database starts with s, for example, the user table: suser (What about sUSER ?), The details are 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 document table, blinux, bcampus
C: special classification layout table, cnewboard
I: base index table, ilinux, ilinux01, icampus, icampus04
J: Excellent article table, jlinux, jcampus,
In addition, are strings or numbers used as identifiers? For example, a sysop account
The id is 1. Is the table of his email msysop or m00001? Similarly, a version called campus corresponds
If the code is 5, will the table name of this article be bcampus or b00005? It may be easy to use strings.
Check the error.
User Info table: suser
Usernum int unique, // unique identifier, which can contain up to 30000 accounts. Will it be too small?
Userid char [20] primary key, // keyword of sorting, id, all lower case.
Passwd char [20], // password, which stores the encrypted ciphertext.
Realid char [20], // actual id, case-insensitive.
Username char [24], // username
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 I need other data? Whether to leave a certain reserved value, and then alter table
// What is the efficiency of adding new fields?
Layout classification table: sclass
Classnum int unique, // category ID
Classid char [20], // english id of the classification: computer
Classname varchar [100], // Chinese description of classification: Computer World
Classtable char [20], // layout table corresponding to the special category
// Generally, each layout belongs to only one category. For special categories, such as the fist section,
// New layout, which can be described using special tables
Layout table: sboard
Boardnum int unique, // layout identifier (required ?)
Boardid char [20], // English name of the layout
Boardname varchar [100], // Chinese name of the layout
Boardclass char [20], // layout category
Boardsysop varchar [100], // bamboo list
Boardposts int, // number of articles on the layout
Boardlevel int, // read and write permissions for the layout
Indextable char [20], // name of the index table corresponding to the layout: aboardid?
Texttable char [20], // The Name Of The article table corresponding to the layout: bboardid?
// Are the last two items necessary? Can they be used as an inevitable correspondence or allowed?
// Is there more flexibility? In addition, can the case sensitivity problem of the layout be directly defaulted?
// Only start with uppercase letters,
Layout of special categories: snewboard and sstarboard
Boardid char [20], // layout id
// Is this table necessary?
Layout index table: acampus, alinux, afootball ......
Id int, // document sequence number, which must be adjusted manually ????
Mark char [1], // Article mark, m, g, B, d ....
Title varchar [100], // article title
Writer char [20], // Author id
Posttime time, // posting time
Textnum longint, // the corresponding number ??? Not adjusted
Layout Article table
Textnum longint, // article number?
Textword text, // article content?
// Is it necessary to separate the index from the document content? In terms of efficiency, lazy flush
// Is inevitable. Delete is also marked first.
// Whether the layout text in the user is unread or not is complex, and whether a heap of tables should be created
// Can it be implemented?
// The voting function is not considered for the moment ....
