The tester's focus on database table design is a means of "shifting testing left" to eliminate potential database-related bugs before the system is tested, thus improving delivery efficiency.
In the case of MySQL, for example, QA can make a left shift in testing the database table design in the following ways:
1. Data sheet function
table structure
- Ensure that each table has a primary key with a unique and non-null value to ensure that each row of data in the table is unique and identifiable
- Check that the foreign key constraint is valid, i.e., the foreign key value must be a value that exists in the corresponding primary key table to avoid isolated data or invalid associations
- If there are associations between tables, verify that the association relationships between tables are correctly established (e.g., whether the primary and foreign keys are set accurately, and whether the data types and lengths of the associated fields are consistent)
- Assess the adaptability of the data table structure to business changes (whether it can be flexibly adapted and extended to meet changing business needs)
table field
- Whether the data type of the field meets the business requirements (e.g., the type of a field with a large amount of data is designed as an int, using unsignedint or bigint)
- Whether the length of the field is designed reasonably (whether there is a reasonable buffer, compatible with subsequent new business)
- Uniformity in the naming of fields with the same meaning in different tables (less ambiguity and less likelihood of bugs when multiple participants are involved in the development)
- Whether the types and lengths of fields with the same meanings are consistent across different tables (if they are not, there may be a bug where Table A falls into the library normally but Table B does not fall into the library normally)
- Whether sensitive characters are encrypted (e.g., bank card numbers, identification numbers, cell phone numbers, etc.)
- Whether the constraints meet the business requirements (the interface design field can be empty, but the table design field NOT NULL will obviously report an error when submitted)
- Whether redundant fields are tailored to actual business requirements (many times, RDs design many invalid word breaks to improve scalability, which can pull down CURD performance)
- If a field has a default value, check that the default value meets the business requirements
- Date word breaks need special attention, whether business requirements for date formatting
2. Data table performance
- For tables that will be manipulated by query operations, are indexes designed (fields that are often used for query conditions, join conditions, or sorting, need to be indexed)
- Whether the creation of indexes is reasonable and effective in improving query performance (the fields for which indexes are designed should be filter fields for query conditions)
- Whether indexes are over-created (sensible indexes can improve query performance, but too many indexes can pull down the performance of additions, deletions, and modifications)
- Whether views are sensible to create (if new tables are added to participate in a join table query, consider whether views can be used to improve query speed)