Database Design Suggestions

Database design suggestions:

1. the table must have an identifier. This is a basic rule. Each table should have a unique row identifier and a set of columns or columns that make the table's records and records different from each other. Each table should have an identifier column, and the values of the identifiers of each record are unique. This row identifier is called a primary key.

2. Tables should only store data of a single instance type. If too much information is stored in the table, you may not be able to effectively and reliably manage the database. For example, in the example database adventureworks of SQL server2005, sales orders and customer information are stored in separate tables. If you place the sales order and customer information in the same table, this design may cause some problems. For example, each sales order may need to repeat and store customer information (name and address), which requires additional database storage space. In addition, if the customer address is changed, the information of each sales order needs to be changed accordingly. In addition, if the last sales order of the customer is removed from the salesorderheader table, all information of the customer may be lost.

3. Do not over-use columns that allow null values. A null value indicates that there is no value. Although a table can define a column that allows a null value, it may be useful to allow a null value in certain conditions, but you should use it with caution. The null value requires special processing and increases the complexity of data operations. When a table contains many columns that allow null values and do not store any data values, we recommend that you store these columns in another table and connect them to the primary table. This makes the primary table easy to design and can still handle emergencies when storing this information.

4. Avoid storing multiple values in a column or multiple columns with the same features. If you want to store multiple data in one column or one row has multiple columns with the same features (for example, telephonenumber1 and telephonenumber2), you can consider placing duplicate data in other tables, the primary table is connected. For example, the adventureworks database has a production function that stores product information. product table, a vendor information Purchasing. the vendor table and a purchasing. the productvendor table. The third table only stores product identifiers and product vendor identifiers. This design can enable more than one product from the supplier without modifying the table definition or allocating additional storage space for the product of a single vendor.