Project-specific summary: 1. Establish a foreign key relationship: maintain data consistency and update synchronization through the foreign key relationship. For example, if the database involves referencing information from another data table, and the database fields (or ID strings) are created repeatedly in this table, the query seems to reduce the table connection's validity period, however, it is difficult to maintain data consistency. For example, when the referenced data changes, the referenced data table still contains the original information, resulting in data redundancy. 2. If there is an association between tables, a foreign key constraint should be established to keep data updated synchronously. For example, if Table 1 uses only the "name" field in table 2, a foreign key relationship should also be established, instead of simply placing the name value in Table 2 into table 1, although SQL is less associated, the efficiency improvement is not obvious. 3. For database design documents, you must save the physical model diagram of the database and only the structure (field) information of the tables in the database. It is difficult to master the logical relationship between database tables efficiently, therefore, it is difficult to directly obtain the executed business SQL when implementing the business. 4. For the database access layer, common access SQL normalization Methods fix the SQL structure and splicing mode. You only need to input parameters in the method parameters to form the SQL, this can simplify the code, make the SQL statements executed by each database operator the same, and ensure that the SQL efficiency is the best at present. If a better SQL statement is found in the future, you can modify only the join and definition methods of SQL in the method. 5. For the data access layer, different methods involve encapsulating a public method for the same interaction with the database for maintenance. For example, you can convert datatable data to the model method and query datatable data sets in the database based on conditions. After encapsulation, you only need to call the encapsulated method to process the data. 6. (Reference) when obtaining all the data, such as querying all the data table content and obtaining the data entity model, all the columns of the data table can be queried in the business, you can use select * instead of select Field 1, Field 2 ..., when fields are added to a data table, the second method needs to be modified in sequence, which is inconvenient for maintenance. 7. Use stored procedures as appropriate, such as the paging method. The storage process (creating temporary tables in the database) may be more efficient. 8. For the definition of Field Types in the model class, the string type can be used, because the string type is easy to convert to other types and flexible to use. In this way, you can unify the ADD and update Methods in the Dal layer. During the SQL concatenation process, you can judge whether each field is null. If it is not null, it is spelled into the SQL. The advantage of being set to string is that some special types, such as null integer and time type, are easy to convert through string processing, but they also have disadvantages, when splicing SQL statements at the database access layer, it is necessary to know which field requires type conversion, such as the time type. Cast conversion is required in sqlserver, to_date is required in Oracle, and timestamp is required in PostgreSQL.
'2017-02-16 20:38:40 '. 9. Create an index for two data tables that store big data or query conditions that are widely used in the business.