MYSQL database Naming and Design Specifications

MYSQL database Naming and Design specifications do you have a headache in obtaining MYSQL database naming and designing specifications? If this is the case, the following articles will give you corresponding solutions. The following articles mainly introduce solutions for obtaining MYSQL database naming and designing specifications, the following is a detailed description of the relevant content. Www.2cto.com 1) standardization and standardization of data helps eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to have achieved the best balance in terms of performance, scalability and data integrity. To put it simply, the table design principle for databases that comply with the 3NF standard is: "One Fact in One Place", that is, a table only includes its basic attributes, when they are not their own properties, they need to be decomposed. The relationships between tables are connected by foreign keys. It has the following features: a group of tables specifically store the associated data connected by keys. Example of www.2cto.com: A 3NF database storing customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the Customer associated with the Order, but stores a key value. The key points to the row in the Customer table that contains the Customer information. In fact, for the sake of efficiency, it is sometimes necessary not to standardize the table. 2) data-driven systems adopt data-driven rather than hard-coded methods. Many policy changes and maintenance operations are much more convenient, greatly enhancing system flexibility and scalability. For example, if you want to access external data sources (files, XML documents, and other databases) on the user interface, you may wish to store the connection and path information in the user interface support table. In addition, if you execute tasks such as workflows on the user interface (such as sending emails, printing letterhead, and modifying record status), the workflow data can also be stored in the database. Role permission management can also be completed through data-driven. In fact, if the process is data-driven, you can push a considerable amount of responsibility to users to maintain their own workflow processes. 3) consider various changes when designing the database, and consider which data fields may change in the future. For example, the last name is the same as the last name of a Western person, for example, the last name of a female after marriage ). Therefore, when a system is created to store customer information, the last name field is stored in a separate data table, and fields such as the start date and end date are appended, in this way, we can track the changes of this data entry. 2. the database consists of 26 English letters (case sensitive) and 10 natural numbers 0-9, with an underscore '_', and 63 characters in length. other characters are not allowed (except comments ). note: 1) the MYSQL database name cannot exceed 30 characters. the variable name length is limited to 29 (excluding the identifier character @). 2) The names of Data Objects and variables are all English characters. Chinese names are prohibited. do not leave spaces between characters in the object name. 3) Be careful when retaining words. Ensure that your field names do not conflict with the reserved words, database systems, or common access methods. 5) ensure that the field names and types are consistent, ensure consistency when naming a field and specifying its data type. if the data type is an integer in one table, the Data Type in the other table should not be converted to the numeric type. 3. database MYSQL database naming rules database, data tables are all composed of lowercase English letters and underscores (_) using the prefix for formal database names. Try to describe the application or system in use. for example, the web_19floor_net web_car backup database name is composed of the official database name and backup time, for example, web_19floor_net_20070403 web_car_20070403 4. database Table MYSQL database naming rules data table names consist of lower-case English letters and underscores. Try to describe that application or system is in use. data Tables of related applications use the same prefix. For example, tables in the Forum use the cdb _ prefix, and data tables in the blog use the supe _ prefix. Generally, the prefix name cannot exceed 5 words. For example: web_user web_group supe_userspace the name of the backup data table is composed of the formal table name and the backup time, for example, web_user_20070403 web_group_20070403 supe_userspace_20070403 5. field MYSQL database naming convention field names are completed by combining words, lowercase letters, followed by uppercase letters, preferably with the table name prefix. for example, the fields in the web_user table: userId userName the related fields between the table and the table must use a uniform name. For example, the userId in the web_user table corresponds to the userId in the web_group table. 6. field Type rule: use up a small amount of storage space to store data of a single field. for example, if you can use int, you don't need char or varchar. If you can use tinyint, you don't need int. If you can use varchar (20), you don't need varchar (255) timestamp fields. Try to use int type, such as created: int seconds starting from '1970-01-01 08:00:00 '. The time format is English words. gmtCreated indicates the time of the datetime type, that is, the time string like '2017-01-01 00:00:00 '. The corresponding type in Java is Timestamp 7. database Design Documents specifications all database designs should be written into documents, which are expressed in modular form. the general format is as follows: '---------------------------------' table name: web_user 'Author: Aeolus (silly fish)' Date: 'version: 1.0' Description: Save User Information 'content: 'userid int, auto increment user code 'username char (12) UserName word '...... '-------------------------------------------- 8. index usage principles: 1) logical primary keys use unique group indexes, system Keys (as stored procedures) use unique non-group indexes, and foreign key columns use non-group indexes. consider the size of the database space, how tables are accessed, and whether these accesses are mainly used for reading and writing. 2) most databases index primary key fields automatically created, but do not forget to index Foreign keys, which are also frequently used keys, for example, running a query to display a record of the master table and all associated tables can be used. 3) do not index blob/text or other fields, or index large fields (with many characters). This will make the index occupy too much storage space. 4) do not index small tables that are commonly used. do not set any keys for small data tables. Do not do this if they often have insert or delete operations. the index maintenance for these insert and delete operations may consume more time than the scan tablespace. 9. SQL statement specifications all SQL keywords are capitalized, such as SELECT, UPDATE, FROM, ORDER, BY, etc. All table names and database names must use ''to include: SELECT COUNT (*) FROM 'cdb _ members 'where 'username' = 'aeolus'; 10. other design skills 1) Avoid using trigger triggers. the trigger may become an interference during program debugging. if you really need to use a trigger, you 'd better document it in a centralized manner. 2) use common English (or any other language) instead of coding or initials. It is best to sort by English name when creating drop-down menus, lists, and reports. if you need to encode or use the initials of the Chinese alphabet, you can attach the English letters that the user knows. 3) it is useful to save common information so that a table can store general database information. store the current database version, recent check/repair (Access), name of the associated design document, and customer information in this table. in this way, a simple mechanism can be implemented to track databases. When customers complain that their databases do not meet the expected requirements and contact you, this is especially useful for non-client/server environments. 4) include the version mechanism to introduce the version control mechanism in the database to determine the version of the database in use. after a long period of time, users' requirements will always change. in the end, you may need to modify the database structure. it is more convenient to store version information directly in the database. 5) Compile the documentation for all shortcuts, MYSQL database naming conventions, restrictions, and functions. database tools that annotate tables, columns, and triggers are used. it is useful for development, support, and tracking and modification. document the database, or create documents in the database itself or separately. in this way, after more than a year, I will go back to the 2nd versions, and the chances of making mistakes will be greatly reduced. 6) After the database is created or revised for testing, testing, and repeated testing, the user must use the new data test data field. the most important thing is to allow users to perform tests and ensure that the selected data type meets the commercial requirements. the test should be completed before the new database is put into the actual service. 7) The common technology used to check the database design during development is to check the database through its supported application prototype. in other words, for each prototype application that ultimately expresses data, you must check the data model and view how to retrieve the data.