After building the development environment, Doking's BLOG database planning is the third step. It is also a key step that is directly related to whether the database can run effectively in the future. It is also an important step for a database. Developer specification requirements.
3.1 Standardization of database
The standardization of database is mainly reflected in the relationship between tables, data structure and storage.
3.1.1 Relationships between data tables
Many beginners often think that it is enough to create a table to store all data. If you think so, it will often cause data redundancy. Figure 3-1-1 is a data table. You can see that the data drawn in the red circle is redundant.
Figure 3-1-1
Redundant data in a data table will affect the effective operation of the database and can even destroy the database. In order to reduce redundant data, the data must be stored separately into several tables, and connections between tables must be established. There are three main ways of connecting relationships between tables: one-to-many, many-to-many, and one-to-one.
One-to-many relationship is the most common relationship type in databases. This relationship is called the parent table and child table relationship or the main table and sub-table relationship. Figure 3-1-2 reflects a parent table and child table relationship. Here, the topic table and the reply table have a one-to-many relationship. The topic table is the parent table, and the reply table is the child table. A row in the topic table can have zero rows, one row, or many corresponding rows in the reply table, and each row in the reply table corresponds to the corresponding topic in the topic table.
Figure 3-1-2 One-to-many relationship A
many-to-many relationship means that each row in the first table can correspond to many rows in the second table. Similarly, each row in the second table can also correspond to many rows in the first table. corresponding to many lines. For example, many users can reply to a topic, and a user can also reply to multiple topics. This requires the creation of a third table to connect the topic codes and user codes. This table is the parent table of the first two tables. In Figure 3-1-3, the "reply table" is the parent table of the "topic table" and "user table".
Figure 3-1-3 Many-to-many relationship
A one-to-one relationship is to connect rows in one table with rows in another table. This relationship is very rare, because if it were, it would be more reasonable to combine the two tables into one table.
3.1.2 Data structure and storage
The physical structure of the database must be established based on the logical structure. Some rules need to be remembered when designing tables, fields and relationships. The order of rows and columns does not play a role. Although columns have a certain sequential position in the data table, they do not represent reference order. The order of records only matters when they are taken from the data table and sent to them. SQL will handle the extraction order of records through the parameter database index. For example, in the data table, it is okay to put the gender field before the name field. As long as you remember to put the name field before the gender field when calling the data table using SQL statements, it will be fine.
When designing a data table, allocate field sizes carefully, and save as much as possible when it is sufficient. If the field is used to store integers, please use integers or long integers instead of single precision or double precision. If it is sufficient, it is best to create a 100-character text type field instead of a memo field. Why? If you are not afraid of high server costs or slow network database operation, you don't have to worry about this, but it will most seriously cause the database to crash.
3.2 Design of Doking's BLOG database
Based on the above knowledge of database standardization, this section begins the planning and design of Doking's BLOG database. Five data tables are designed in the Doking's BLOG database, namely LM table (column table), ZT table (study note table), HF table (reply table), YH table (user table), and GLY table (administrator table). And name the database "dkblog.mdb" and save it in the "D:dat" directory.
The LM table is a column table, which is used to create website navigation bars and classify study notes. The column content is designed by the administrator of the website. Its field design is shown in Figure 3-2-1.
Figure 3-2-1 Field design diagram of the LM table.
The ZT table is a learning note table, which is used to store users’ learning experience. In fact, the field design is shown in Figure 3-2-2:
Figure 3-2-2 Field design diagram of the ZT table.
The HH table is a reply table, which stores users' responses to published study notes. Its fields are shown in Figure 3-2-3:
Figure 3-2-3 Field design diagram of the HF table.
The YH table is the user table, which stores user registration information and facilitates users to log in and post study notes and replies to study notes. Its field design is shown in Figure 3-2-4:
Figure 3-2-4 Field design diagram of YH table
GLY table is the administrator table, which is used for administrator login and website management. Its field design is as shown in Figure 3-2-5
Figure 3-2-5 Field design diagram of GLY table
3.3 Create Doking's BLOG database connection source
(1) Start Dreamweaver MX 2004, in the [File] menu, select [New], in the [New Dialog], select the category as "Dynamic Page", select the dynamic page as "ASP.NET VB", and click "Create" ” button to create a dynamic web page.
(2) Switch to the [Application] panel, select the [Database] tab, click the "+" sign, and select "OLE DB Connection", as shown in Figure 3-3-1:
Figure 3-3-1 Select OLE DB connection
(3) In the pop-up [OLE DB Connection] dialog box, enter the connection name "dkconn" and click the "Create" button, as shown in Figure 3-3-2:
Figure 3-3-2 Set the connection name
(4) Enter the [Data Connection Properties] page and select the [Select Program] tab. Since the database to be connected is an Access2003 database, select "Microsoft Jet 4.0 OLE DB" in the database engine. Provider" as the driver, as shown in Figure 3-3-3:
Figure 3-3-3 Select database engine
(5) Select the [Connect] tab, select the database to be connected as "D:dkblogdatdkblog.mdb", and clear the text boxes of "Username" and "Password" , Figure 3-3-4
Figure 3-3-4 Select to connect to the database
(6) Select the [Advanced] tab, and in setting access permissions, select "ReadWrite", as shown in Figure 3-3-5
Figure 3-3-5 Set access permissions
(7) Return to the [Connect] tab, click the "Test" button, and the dialog box shown in Figure 3-3-6 will appear, which means that the database dkblog.mdb has been connected to the site. . Click the "OK" button to complete the database connection. Under the root directory of the site, you can see that a new file "web.config" has been added. You can directly modify the database connection in this file in the future.
Figure 3-3-6 Database test dialog box
If you click the [Create] button in step (3) of Section 3.3, the [Data Link Properties] dialog box Not open. Nothing happens when clicking the [Create] button. One possible cause of this problem is MDAC 2.8; MDAC 2.8 is included by default in XP SP2. XP SP1 users who have MDAC 2.8 installed are also affected by this issue. This issue only affects Dreamweaver MX 2004 and has no impact on Dreamweaver MX. There are two solutions:
The first method: follow step (3) of the steps in Section 3.3, and enter in the [Connection String] text area in the [OLE DB Connection] dialog box:
Provider=Microsoft.Jet. OLEDB.4.0;DataSource=D:dkblogdatdkblog.mdb;Persist Security Info=False
Second method: Create a new text file on the hard disk, change the file extension from .txt to .udl, and then Open the .udl file. This file can open the [Data Link Properties] dialog box. The specific operations are as follows:
① Follow step (3) of the steps in Section 3.3. Execute the [Start] | [All Programs] | [Accessories] | [Notepad] system menu command.
② In the main window of the Notepad program, directly execute the [File] | [Save] menu command. In the pop-up [Save As] dialog box, the input file name is database connection.udl, as shown in Figure 3-4-1.
Figure 3-4-1 Save "Database Connection.udl"
③Click the [Save] button, close the Notepad program, switch to [Desktop], double-click the "Database Connection" file, follow steps (4)-(7) in the tutorial ) to operate. Click the [OK] button to close the [Database Link] dialog box.
④Execute the [Start] | [All Programs] | [Accessories] | [Notepad] system menu command. In the main window of the Notepad program, directly execute the [File] | [Open] menu command and select "Database Connection" to open the file.
⑤ In the main window of the Notepad program, select all the characters in the third line to copy, as shown in Figure 3-4-2. Switch to the Dreamweaver program and paste it into the [Connection String] text area in the [OLE DB Connection] dialog box that pops up in step (3). This completes the database link in Dreamweaver.
Figure 3-4-2 Copy all characters in the third line of the "Database Connection" file