For a forum or a message board, there are generally two ways to achieve it: using a file or a database. In contrast, using a database to be simple and efficient, to better control the forum, but also to provide some validation and protection of data.
Access databases are used here, and access should be competent for small and medium sized applications in general.
From the above analysis to know that there should be four tables, below I give the structure of each table.
Author table (store author information):
ID: Text type, required. is the user code.
Password: text type, required.
Nickname: Text type, required.
EMAIL: Text type, required.
Title: Number type, required. -1 on behalf of ordinary users, 0 for the webmaster. Numbers greater than 0 represent the owner, and the number is the ID of a board in the corresponding Kanban table, indicating the owner of the board.
Article number: Number type, required. The total number of articles published by Netizens.
Name: text type, optional.
Sex: Text type, optional.
Tel: text type, optional.
Content table (for specific article content and related information):
ID: Automatically numbered and indexed to speed up the search.
Kanban ID: A number type from a Kanban list that represents the Kanban that the article belongs to.
Subject ID: A numeric type from a topic table that represents the topic to which the article belongs.
Author ID: Text type, from the author table, representing the author of the article.
Date: Date/Time type, preset initial value is function now (), when the system adds this field, the system will automatically take the current time for its values.
Title: Text type. The title of the article.
Publish: Yes/no type, for "true" when the article has been reviewed and can be published; "No" to indicate that the article is yet to be reviewed.
Recommendation: Number type, the recommended degree of the article.
Content: Memo type, article details.
Number of clicks: Number type, the number of clicks on the article.
Kanban list (information about Kanban):
ID: Automatically numbered, and the index is also set for it.
Name: text type, name of Kanban.
Board main: Text type, kanban owner ID.
Subject number: Number type, number of topics included in Kanban.
Topic table (For information on topics):
ID: Automatically number and index it.
Title: Text type, representing the topic name.
Kanban: A number type from a Kanban list that represents the Kanban to which the subject belongs.
Article count: Number type, the number of articles included in the topic.
All the tables are finished, but the database design is not finished. We also need to establish a relationship between the tables so that the database can perform some correlation checks to avoid data errors. Another benefit of establishing relationships between tables is that it makes it easy to create complex join queries.
In the table above, you can easily see the relationships between these tables. You can see from the following figure how these relationships relate to each table.
Typically, when we manipulate a database in ASP, we are using runtime-generated queries that are then passed to the database to interpret the execution. And here we're going to use a storage query. Compared with run-time query, the storage query has more advantages.
It is stored in the database, independent of the ASP code, making it easier to build and modify, and query more efficient, faster, you can debug the later put on the ASP page call, can avoid many problems. And the ASP code that uses the storage query is easier to read and modify. It may be annoying to call SQL queries in ASP, especially those commas, semicolons, and so on. When you use a storage query, you don't have to worry about these issues. Of course, the storage query reuse there are some places to pay attention to, in the following I will explain the use of its methods.
It's easy to create a storage query in Access, and I'm not going to talk about it any more. If you do not know, please refer to the use of storage query, where I only give the SQL statement code for each query.
I keep all the queries that I want to use as a storage query in the database, with the following main:
Ann ID Inquiry article:
SELECT topic table. Title as topic name, Kanban list. Name as Kanban name, Content table. *
From topic table INNER Join (content table INNER join Kanban list on content table. Kanban ID = Kanban list. id) on topic table. ID = content table. Subject ID
WHERE ((Content table. ID) =[articleid]);
Owner password Query:
SELECT kanban list. Board Master, author table. Password
From author table INNER JOIN kanban list on author table. ID = kanban list. Board Master
WHERE ((kanban list. ID) =[id];
Query Author:
SELECT Author table. *
From author table
WHERE ((author table. ID) =[id]);
Publish a list of articles:
SELECT [Content table]. [ID], [content table]. [title], [content table]. [Author ID] As author, [Content table]. [Date], [content table]. [Recommended degree], [content table]. [Number of clicks] As number of clicks
From content table
WHERE (([[Content Table].[ Subject ID]) =[topicindex] and ([Content table].[ )) (=true));
List of articles not published:
SELECT content table. ID as article ID, subject table. ID as topic ID, subject table. Title as topic, Content table. Title as title, Content table. Author ID as author, content table. Date as Date
From topic table INNER JOIN Content table on topic table. ID = content table. Subject ID
WHERE ((Content table. Published) =false) and ((Content table. Kanban ID) =[boardid]);
Topic list:
SELECT topic table. *, Kanban list. Name as Kanban name
From Kanban list INNER JOIN topic table on Kanban list. ID = Topic table. kanban
WHERE ((Subject table. Kanban) =[boardindex]);
There are also some inquiries, because most similar, I will not list them.
In the above query, you can see some things surrounded by "[" and "]", that is, query parameters. You need to give the parameter values at run time, and then bring the parameter values into the query statement to be able to run. It is also important to note that when creating those inner join queries, it is necessary to add the relationship between the tables to the Design view, otherwise it is not possible to automatically generate INNER JOIN query statements.
Here, the database design is over, the future work is the ASP.
