Chapter 7 : Design your web database
In this chapter, we need to discuss three problems in order to get into MySQL specifics in the next chapter:
[1] Ralational database concepts and terminology (数据库相关概念和术语)
[2] Web database design (数据库设计)
[3] Web databse architecture (数据库的架构)
The first problem : Ralational database concepts
[Tables]
A table should hava a table_name, a number of columns, a number of rows, each col is corresponding to a different piece of data, each row is corresponding to an individual data.
(一个表有表名、列、行,每一列对应不同数据,每一行对应一个数据的属性)
[Columns]
Each column in the table has a unique name and contains different data.
Each column has an associated data type.
Because of this, columns are sometimes called fields or attributes.
(列常被称为“域”或“属性”)
[Rows]
Each row has the same data type and the same attributes.
Because of this, rows are called records or tuples.
(行常被称为“记录”或“元组”)
[Values]
Each row consists of a set of individual values that correspond to columns.
Each value must have the data type specified by its column.
(每一行由对应于每一列的单个值组成(行与列交叉的地方就是值);每一个值都必须与该列定义的数据类型一样)
[Keys]
Key is a way to identify each specific data.
The identifying column in a table is called the key or the primary key.
A key can consist of multiple columns.
Databases usually consist of multiple tables and use a key as a reference from one
table to another.
[Schemas]
Schemas mean that the complete set of the table designs for a database.
[Ralationships]
Foreign keys represent a relationship between data in two tables.
Relationships can be either one-to-one, one-to-many, or many-to-many.
The second problem : Web database design
[1] Think about the real world objects you are modeling (考虑要建模的真正对象)
[2] Avoid storing redundant data (避免数据重复)
[3] Use atomic column values (使用原子列值)
[4] Choose sensible keys (选择有意义的主键)
[5] Think about the questions you want to ask the database (我们要用这个数据库解决的问题)
[6] Avoid designs with many empty attributes (避免空值)
The third problem : Web database architecture
[1] A user's Web browser issues an HTTP request for a particular Web page.
[2] The Web server receives the request, retrieves the file(xxx.php), then passes it to the PHP engine for processing.
[3] The PHP engine begins parsing the script. PHP opens the connection to the MySQL server and sends on the appropriate query.
[4] The MySQL server receives the database query and process it, and sends the results back to the PHP engine.
[5] The PHP engine finishes running the script, which will usually involve formatting the query results nicely in HTML. It then returns the resulting HTML to the Web server.
[6] The Web server passes the HTML back to the browser.