sql - 帮助数据库规范化的工具或网站

Question

关闭。这个问题不满足Stack Overflow guidelines .它目前不接受答案。












想改善这个问题吗？更新问题，使其成为 on-topic对于堆栈溢出。

5年前关闭。




Improve this question




是否有任何工具或在线资源(FREX 教程)可以帮助初学者进行数据库规范化？

Answer 1

An Introduction to Database Normalization

维基:Database normalization

Database Normalization Tips

Achieving a Well-Designed Database In relational-database design theory, normalization rules identify certain attributes that must be present or absent in a well-designed database. There are a few rules that can help you achieve a sound database design:

• A table should have an identifier. The fundamental rule of database design theory is that each table should have a unique row identifier, a column or set of columns used to distinguish any single record from every other record in the table. Each table should have an ID column, and no two records can share the same ID value. The column or columns serving as the unique row identifier for a table are the primary key of the table. In the AdventureWorks database, each table contains an identity column as the primary key column. For example, VendorID is primary key for the Purchasing.Vendor table.

• A table should store only data for a single type of entity. Trying to store too much information in a table can hinder the efficient and reliable management of the data in the table. In the AdventureWorks sample database, the sales order and customer information is stored in separate tables. Although you can have columns that contain information for both the sales order and the customer in a single table, this design leads to several problems. The customer information, name and address, must be added and stored redundantly for each sales order. This uses additional storage space in the database. If a customer address changes, the change must be made for each sales order. Also, if the last sales order for a customer is removed from the Sales.SalesOrderHeader table, the information for that customer is lost.

• A table should [try to] avoid nullable columns. Tables can have columns defined to allow for null values. A null value indicates that there is no value. Although it can be useful to allow for null values in isolated cases, you should use them sparingly. This is because they require special handling that increases the complexity of data operations. If you have a table with several nullable columns and several of the rows have null values in the columns, you should consider putting these columns in another table linked to the primary table. By storing the data in two separate tables, the primary table can be simple in design and still handle the occasional need for storing this information.

• A table should not have repeating values or columns. The table for an item in the database should not contain a list of values for a specific piece of information. For example, a product in the AdventureWorks database might be purchased from multiple vendors. If there is a column in the Production.Product table for the name of the vendor, this creates a problem. One solution is to store the name of all vendors in the column. However, this makes it difficult to show a list of the individual vendors. Another solution is to change the structure of the table to add another column for the name of the second vendor. However, this allows for only two vendors. Additionally, another column must be added if a book has three vendors. If you find that you have to store a list of values in a single column, or if you have multiple columns for a single piece of data, such as TelephoneNumber1, and TelephoneNumber2, you should consider putting the duplicated data in another table with a link back to the primary table. The AdventureWorks database has a Production.Product table for product information, a Purchasing.Vendor table for vendor information, and a third table, Purchasing.ProductVendor. This third table stores only the ID values for the products and the IDs of the vendors of the products. This design allows for any number of vendors for a product without modifying the definition of the tables, and without allocating unused storage space for products with a single vendor.

Ref .