Database design experience talk about database other

A successful management system is made up of: [50% of the Business + 50% software], and 50% of the success of the software has [25% database + 25% of the program], the database design is a good or bad is a key. The database design is the most important part of the application if the data of the enterprise is compared to the blood necessary for life. There are numerous materials on database design, and there are also special lectures in university degrees. However, as we have repeatedly stressed, a good teacher is more than a lesson in experience. So I summed up the past years to take the detour and experience, and on the Internet to find some of the database design accomplished professionals to teach you some design database skills and experience. The 60 best tips were selected and written into this article for easy indexing of its content into 5 parts:

Part 1th-Before designing the database
This section lists 12 basic techniques, including naming conventions and clarifying business requirements.
Part 2nd-Design Database tables
A total of 24 guidance tips covering field design in the table and common problems that should be avoided.
Part 3rd-Select key
How to choose the key? Here are 10 techniques that specifically relate to the correct usage of system-generated primary keys, and when and how to index fields for optimal performance.
Part 4-Ensuring data integrity
Discuss how to keep the database clear and robust, and how to minimize harmful data.
Part 5th-Various tips
Other techniques that are not included in the 4 sections above are diverse, and they want your database development work to be easier.
Part 1th-Before designing the database
Review the existing environment
When designing a new database, you should not only study the business requirements carefully but also examine the existing systems. Most database projects are not built from scratch; there are always existing systems in the organization that meet specific requirements (and may not implement automated computations). Obviously, the existing system is not perfect, otherwise you won't have to build a new system. But the study of the old system allows you to find some subtle problems that may be overlooked. In general, it is absolutely good for you to look at existing systems.
Defining the standard object naming conventions
Be sure to define the naming conventions for database objects. For a database table, it is important to determine from the beginning of the project whether the table name is in plural or singular form. You also have to define a simple rule for the alias of the table (for example, if the table name is a word, the alias takes the first 4 letters of the word, and if the table name is two words, it takes the first two letters of two words to form a 4-letter alias; If the table's name is made up of 3 words, You might as well take one from the first two words and then remove two more letters from the last word, and the result is a 4-letter alias, and so on. For work tables, the table name can be prefixed Work_ with the name of the application using the table appended. The columns in the table [fields] use a set of design rules for the key. For example, if the key is a numeric type, you can use _n as the suffix, and if it is a character type, you can use the _c suffix. You should use the standard prefix and suffix for the column [field] name. Again, if you have a lot of "money" fields in your table, you might add a _m suffix to each column [field]. Also, a date column [field] is best preceded by a d_ name.

Check the naming convention between the table name, report name, and query name. You may soon be confused by the names of these different database elements. If you insist on uniformly naming the different components of these databases, at least you should distinguish them by prefixes such as Table, Query, or the name of the header at the beginning of the names of those objects.

If you are using Microsoft Access, you can identify objects (such as tbl_employees) with symbols such as qry, RPT, TBL, and mod. I used TBL to index tables when I was dealing with SQL Server, but I used Sp_company (now Sp_feft_) to identify the stored procedures, because sometimes I would save several copies if I found a better approach. I used udf_ (or similar tags) to identify the functions I wrote when I implemented SQL Server 2000.
工欲善其事, its prerequisite
Using the ideal database design tools, such as: SyBase Company's powerdesign, she supports the PB, VB, Delphe and other languages, through the ODBC can connect to the market popular more than 30 databases, including DBase, FoxPro, VFP, SQL Server, etc. I will highlight the use of powerdesign in the future.
Get Data Schema Resource Manual
People looking for sample patterns can read the Data Mode resource manual, written by Len Silverston, W. Inmon and Kent Graziano, as the best data modeling book worth having. The book includes chapters covering a wide range of data areas, such as people, institutions, and work efficiency. Other You can also refer to: [1] Shaman Xuan Wang Shan Database System Introduction (second Edition) Higher Education Press 1991, [2][United States] Steven M.bobrowski Oracle 7 with client/server computing technology from getting started to proficient in Liu Jianyuan and other translation Electronics publishing House, 1996, [ 3] Zhou-Zhong meta Information System modeling Method (next) Electronics and Informatization 1999, 3rd, 1999
Imagining the future, but not forgetting the lessons of the past
I find it useful to ask users how they think about future changes in demand. This can be done for two purposes: first, you can clearly understand where the application design should be more flexible and how to avoid performance bottlenecks, and second, you know that users will be as surprised as you are when there is no predetermined requirement change.

Be sure to remember the lessons of the past! We developers should also help each other by sharing their experiences and experience. Even if the user thinks they don't need any more support, we should educate them about it, and we've all faced the moment when "it would have been so much better ...".
Logical design prior to physical practice
Make logical design before delving into the physical design. As a large number of case tools emerge and your design can reach a reasonably high level of logic, you will generally have a better understanding of all the aspects of database design as a whole.
Get to know your business
Do not add even one datasheet to your ER (Entity Relationship) pattern before you make sure that the system meets its needs from the customer's perspective (why, you don't have a pattern?) Then please refer to tip 9. Understand that your business can save a lot of time in the future development phase. Once you've identified your business needs, you can make a lot of decisions on your own.

Once you think you have identified the business content, you'd better have a systematic communication with the customer. Use the customer's terminology and explain to them what you think and what you hear. It is also necessary to express the relational cardinality of the system in terms of possible, will and must. This will allow your client to correct your own understanding and then do the next ER design.
Create a data dictionary and ER chart
Be sure to take some time to create ER charts and data dictionaries. At a minimum, you should include the data type for each field and the primary foreign key in each table. It's a bit time-consuming to create ER charts and data dictionaries, but it's absolutely necessary for other developers to understand the entire design. The earlier you create, the more you can help avoid the potential confusion that you face in the future, so that anyone who knows the database can be clear about how to get data from the database.

Having a recent document, such as an ER chart, is not too important to emphasize, which is useful for indicating the relationship between tables, and the data dictionary describes the purpose of each field and any aliases that may exist. This is absolutely necessary for the documentation of SQL expressions.
Create pattern
A chart is more than words: A developer should not only read and implement it, but also use it to help himself and the user talk. Pattern helps to improve collaboration performance, so it is almost impossible to have big problems in the database design in advance. The pattern doesn't have to be complicated; it can even be written on a piece of paper. Just to ensure that the logical relationship in the future can produce benefits.
Start from input and output
When defining database tables and field requirements (input), you should first examine existing or designed reports, queries, and views (outputs) to determine which tables and fields are necessary to support these outputs. A simple example: if a customer needs a report to sort, segment, and sum the ZIP code, make sure to include a separate ZIP code field instead of Leelawadee the ZIP code into the Address field.
Reporting Tips
To understand how users typically report data: Batch processing or online submission of reports? Is the time interval daily, weekly, monthly, quarterly, or yearly? You can also consider creating a summary table if you want. System-generated primary keys are difficult to manage in the report. Users often return many duplicate data in a table with a system-generated primary key by using a key to retrieve them. This kind of retrieval performance is relatively low and can easily cause confusion.
Understand customer needs
This should seem obvious, but the demand is from the customer (from the perspective of both internal and external customers). Do not rely on the user to write down the needs of the real demand in the customer's head. You need to let customers explain their needs, and as development continues, ask customers frequently to ensure that their needs are still being developed. A constant truth is that "only when I see it do I know what I want" is bound to lead to a lot of rework, because the database does not meet the requirements that customers have never written down. And even worse, your interpretation of their needs is only yours, and may be completely wrong.
Part 2nd-Design tables and fields
Check for various changes
When I design a database, I take into account which data fields may change in the future. For example, the surname is the case (note is the western surname, such as women married after the husband surname, etc.). So, when building a system to store customer information, I tend to store last name fields in a separate table, plus fields such as start and end days so that you can track changes in this data entry.
Use a meaningful field name
Once I was involved in developing a project, there are programs that are inherited from other programmers, and that programmer likes to display data on the screen to name the field, which is fine, but unfortunately she also likes to use some strange nomenclature, named after the Hungarian name and control serial number of the combination form, such as Cbo1, TXT2, Txt2_b and so on.
Unless you are using a system that only targets your abbreviated field names, make the fields as clear as possible. Of course, do not overdo it, such as customer_shipping_address_street_line_1, although very descriptive, but no one would like to type such a long name, the specific scale in your grasp.
Naming with prefixes
If multiple tables have many fields of the same type (such as FirstName), you might want to use a particular table prefix (such as cuslastname) to help you identify the field.

The timeliness data should include the recently updated Date/Time field. Time markers are especially useful for finding data problems, reprocessing/overloading data by date, and purging old data.
Standardization and data-driven
The standardization of data is not only convenient for itself but also convenient for others. For example, if your user interface is to access external data sources (files, XML documents, other databases, etc.), you may want to store the corresponding connection and path information in the User Interface support table. Also, if the user interface executes tasks such as workflow (sending mail, printing letterhead, modifying record status, etc.), the data that generates the workflow can also be stored in the database. Scheduling always requires effort, but if these processes are data-driven rather than hard-coded, policy changes and maintenance will be much more convenient. In fact, if the process is data-driven, you can push a lot of responsibility to the user and the user to maintain their workflow process.
Standardization cannot be overdone
For those unfamiliar with the term standardization (normalization), standardization ensures that the fields within a table are the most basic elements, and this helps to eliminate data redundancy in the database. There are several forms of standardization, but third normal Form (3NF) is often considered to be the best balance in performance, extensibility, and data integrity. In simple terms, 3NF stipulates:
* Each value in the table can only be expressed once.
* Each row in the table should be uniquely identified (with a unique key).
* Non-key information that depends on other keys should not be stored in the table.
Databases adhering to the 3NF standard have the following characteristics: There is a set of tables that specifically store the associated data connected by a key. For example, a 3NF database that holds customers and their related orders may have two tables: Customer and order. The order table does not contain any information about the customer that the orders are associated with, but there is a key value in the table that points to the row in the customer list that contains the client information.
There are higher levels of standardization, but is it better to be more standard? The answer is not necessarily. In fact, for some projects, even 3NF can introduce too much complexity into the database.

For efficiency reasons, it is sometimes necessary to standardize the tables, and there are many examples. Once there was a development of the catering analysis software is to use non-standard tables to reduce the query time from an average of 40 seconds to about two seconds. Although I have to do this, but I do not regard the non-standard data tables as a course of design philosophy. And the specific operation is only a derivation. So if the table is out of the question it is entirely possible to recreate the non-standard table.
Microsoft Visual FoxPro Reporting Tips
If you are using Microsoft Visual FoxPro, you can replace the name of the number with a user-friendly field name: For example, replace Txtcnam with the customer name. This way, when you create forms and reports using the wizard [Wizards, Taiwanese called ' sprites '], their names make it easier for those who are not programmers to read.
An indicator that is inactive or not adopted
Adding a field to indicate whether the record is inactive in the business is useful. Whether it's a customer, an employee, or someone else, doing so can help filter the active or inactive state when you run the query again. It also eliminates some of the problems that new users face when they use data, for example, some records may no longer be used by them, and when they are deleted, they can play a preventive role.
Use role entities to define columns [fields] that belong to a category
When you need to define things that belong to a particular category or have a specific role, you can use a role entity to create a specific time association relationship that enables you to document yourself.
The implication here is not to have the person entity with the Title field, but rather to say, why not use the persons entity and the Person_type entity to describe people? For example, when John Smith, Engineer promoted to John Smith, Director and eventually climbed to John Smith, the CIO's high, all you had to do was change the bond between the two table person and the Person_type Value, while adding a date/Time field to know when the change occurred. In this way, your Person_type table contains all the possible types of person, such as associate, Engineer, Director, CIO, or CEO.
Another alternative would be to change the person record to reflect the change in the new title, but it would not be possible to track the time of the individual's position in time.
Using common entity naming Authority data
The easiest way to organize your data is to use commonly used names, such as person, organization, address, and PHONE, and so on. When you combine these commonly used generic names or create specific corresponding pairs of entities, you get a special version of your own. The main reason for using general terminology at the outset is that all the specific users can materialize abstract things.
With these abstract representations, you can use your own special name in level 2nd identification, for example, the person may be Employee, spouse, Patient, Client, Customer, Vendor, or Teacher. Similarly, organization may also be MyCompany, mydepartment, competitor, hospital, Warehouse, government, etc. The final address can be specific for Site, Location, Home, Work, Client, Vendor, Corporate and Fieldoffice.
Using generic abstract terms to identify "things" can give you tremendous flexibility in associating data to meet business requirements, while doing so can significantly reduce the amount of redundancy required for data storage.
Users from all over the world
When designing a database that uses a network or other international features, be sure to remember that most countries have different field formats, such as postal codes, and some countries, such as New Zealand, do not have a zip code.
Data duplication requires the use of discrete data tables
If you find yourself typing data repeatedly, create a new table and a new relationship.
3 useful fields that should be added to each table
* Drecordcreationdate, in VB under the default is now (), and in SQL Server defaults to GETDATE ()
* Srecordcreator, defaults to not NULL default USER under SQL Server
* Nrecordversion, record version mark, help to accurately explain the occurrence of NULL data or missing data in the record
Use multiple fields for addresses and telephones
It is not enough to describe a street address on a single line. Address_line1, Address_line2 and Address_line3 can provide greater flexibility. Also, phone numbers and mailing addresses have their own data tables, with their own types and tag categories.

Be careful about standardizing too much, which can lead to performance problems. Although address and phone table separation can usually be optimal, if you need to access such information frequently, it may be more appropriate to store "preferred" information (such as Customer, etc.) in its parent table. The compromise between non-standard and accelerated access is of some significance.
Use multiple name fields
I was surprised that many people left a field in the database for name. I think only developers who are just getting started will do this, but it's actually very common online. I suggest that you should treat last name and first name as two fields and then combine them in the query.

My most common use is to create a computed column [field] in the same table, which automatically connects the normalized fields so that the data changes as they change. However, this is tricky to do with modeling software. In short, connecting fields can be an effective way to isolate user applications and developer interfaces.
Beware of case-by-case mixing of object names and special characters
One of the things that annoyed me the most in the past is that the database has a mixture of uppercase and lowercase object names, such as CustomerData. This problem exists from Access to an Oracle database. I don't like the use of this kind of case-sensitive object naming method, the result has to manually modify the name. Think about the day that this database/application can get mixed in with a more powerful database? Names with all caps and the following strokes are better readable (customer_data), and never leave spaces between the characters of the object name.
Be careful with the reserved words
To ensure that your field name does not conflict with reserved words, database systems, or common access methods, for example, I recently wrote a table in an ODBC connector that uses DESC as the Description field name. The consequences are conceivable! DESC is a reserved word after the descending abbreviation. A SELECT * statement in the table is useful, but I get a lot of useless information.
Maintain consistency of field names and types
You must ensure consistency when you name a field and specify the data type for it. If the field is called "Agreement_number" in a table, you should not change the name to "Ref1" in the other. If the data type is an integer in one table, then the other table can not be a character type. Remember, when you finish your work, others will use your database.
Select a number type carefully
Use smallint and tinyint types in SQL with special care, for example, if you want to see the total monthly sales, your total field type is smallint, then if the total exceeds the $32,767 you can not do the calculation operation.
Delete tag
Include a delete tag field in the table so that you can mark the row for deletion. Do not delete a row individually in a relational database, preferably with a data-clearing program and carefully maintain the integrity of the index.
Avoid using triggers
The functionality of triggers can often be implemented in other ways. Triggers can become interference when the program is debugged. If you really need to use triggers, you'd better focus on documenting them.
Include version mechanism
It is recommended that you introduce a version control mechanism in your database to determine the version of the database in use. In any case you have to fulfill this requirement. A long time, the user's needs will always change. The database structure may eventually be required to be modified. Although you can check the new field or index to determine the database structure of the version, but I found that the release of information directly to the database is not more convenient?
Sufficient margin to text field
A text field of type ID, such as a customer ID or order number, should be set up more than you normally think, because you'll probably be embarrassed to add extra characters for a short time. Let's say your customer ID is 10 digits long. Then you should set the database table field length to 12 or 13 characters long. Is that a waste of space? It's a bit, but it's not as much as you think: A field that's 3 characters long. In the case of 1 million records, plus a little index, the entire database occupies 3MB more space. But this extra space does not need to refactor the entire database in the future to achieve the growth of the database scale. The number of ID cards changed from 15 to 18 is the best and most painful example.
column [field] naming tips
We found that if you used a uniform prefix for the column [field] names for each table, you would be greatly simplified when writing SQL expressions. There are drawbacks to doing this, such as breaking the automatic table join tool, which links common column [field] names to some databases, but even these tools sometimes don't connect incorrectly. For a simple example, suppose there are two tables:
Customer and order. The Customer table prefix is cu_, so the child segments in the table are named as follows: cu_name_id, Cu_surname, Cu_initials, and cu_address. The Order table prefix is or_, so the child segment name is:
or_order_id, or_cust_name_id, Or_quantity and Or_description.
The SQL statement that selects all the data from the database can be written as follows:
Select * from Customer, order Where cu_surname = "MyName";
and cu_name_id = or_cust_name_id and or_quantity = 1
This is written in the absence of these prefixes (distinguished by aliases):
Select * from Customer, order Where customer.surname = "MyName";
and customer.name_id = order.cust_name_id and order.quantity = 1
The 1th SQL statement does not have a few characters to type. But if the query involves 5 tables and even more columns [fields] You know how useful this technique is.
Part 3rd-Select keys and Indexes
Data mining must be planned in advance
One of my client departments had to deal with over 80,000 contacts at a time and fill in the necessary data for each customer (this is definitely not a small job). I also want to identify a group of customers as a marketing target. When I was designing tables and fields from the very beginning, I tried not to add too many fields to the main index to speed up the database. Then I realized that specific group queries and information mining were neither accurate nor fast. As a result, the data fields are rebuilt and merged in the primary index. I've found that there's a key to the plan--why do I use numbers as the primary index when I want to create a system type lookup? I can retrieve it with a fax number, but it's almost as much a system type as it doesn't matter to me. Using the latter as the primary field, the database is indexed and retrieved more quickly after it is updated.

There are differences in data indexing between operational data warehouses (ODS) and Data warehousing (DW) environments. In the DW environment, you need to consider how the sales department organizes sales activities. They are not database administrators, but they determine the key information in the table. Here the designer or database worker should analyze the database structure to determine the best conditions between performance and correct output.
Using system-generated primary keys
This technique is similar to 1, but I think it is necessary to remind everyone here. If you always use the system-generated key as the primary key when designing the database, you actually control the index integrity of the database. In this way, the database and the non human mechanisms effectively control access to each row in the stored data.
The advantage of using a system-generated key as a primary key is that it is easy to find a logical defect when you have a consistent key structure.
Exploded fields are used for indexing
To isolate named fields and include fields to support user-defined reports, consider factoring other fields (even primary keys) to make them available for users to index. Indexing speeds up the execution of SQL and Report Builder scripts. For example, I usually create a report if I have to use an SQL like expression because the Case Number field cannot be decomposed into elements such as year, serial number, cases type, and defendant code. Performance can also go bad. If the year and type fields can be decomposed into indexed fields, then these reports will run much faster.
Key Design 4 principle
* Create a foreign key for the associated field.
* All keys must be unique.
* Avoid using composite keys.
* A foreign key is always associated with a unique key field.
Don't forget the index.
An index is one of the most efficient ways to get data from a database. 95% of database performance problems can be solved by indexing technology. As a rule, I usually use a unique group index on a logical primary key, a unique, nonclustered index on the system key (as a stored procedure), and a non group index on any foreign key column [field]. However, the index is like salt, too much food is salty. You have to think about how large the database is, how the tables are accessed, and whether they are primarily used for reading and writing.

Most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used keys, such as running a query that shows a record of the primary table and all associated tables. Also, do not index memo/note fields, and do not index large fields (with many characters), which makes the index occupy too much storage space.
Do not index commonly used small tables
Do not set any keys for small data tables, even if they often have inserts and deletes. The index maintenance for these insert and delete operations may consume more time than the scan table space.
Do not select the Social Security Number (SSN) or ID number (ID) as a key
Never use the SSN or ID as the key to the database. In addition to privacy reasons, it is increasingly necessary for the government to not allow the SSN or ID to be used in addition to income-related purposes, SSN or ID need to manually input. Never use a manually typed key as the primary key, because once you enter the error, the only thing you can do is to delete the entire record and start from scratch.

When I cracked someone else's program, I saw many people who used the SSN or ID as a serial number, although it was illegal to do so. And people know it's illegal, but they're used to it. Later, as the crime of identity theft increased, my current peers were painfully removing the SSN or ID from a large stall of data.
Do not use the user's key
When deciding what field to use as a key for a table, be sure to be careful what fields the user will edit. In general, do not select a user-editable field as the key. This will force you to take the following two measures:
* Imposes restrictions on the behavior of user-edited fields after the record is created. If you do, you may find that your application is suddenly changing in business requirements and users need to edit those non-editable fields without enough flexibility. What do they think when the user enters the data and then saves the record before discovering that the system is out of order? Delete Rebuild? If the record is not rebuilt, do you let the user walk away?
* Some methods for detecting and correcting key conflicts are presented. Usually, it takes a bit of energy to get it done, but in terms of performance It's a lot more expensive. Also, key corrections may force you to break through the isolation between your data and the business/user interface layer.
So it's an old saying: your design needs to be adapted to the user rather than to the user to adapt to your design.

The reason that you do not make the primary key updatable is that in relational mode, the primary key implements an association between the different tables. For example, the Customer table has a primary key CustomerID, and the client's order is stored in another sheet. The primary key of the order table may be a combination of OrderNo or OrderNo, CustomerID, and dates. Regardless of which key setting you choose, you will need to store CustomerID in the order form to ensure that you can find the order records for the user who orders it.
If you modify the CustomerID in the Customer table, you must find all relevant records in the order list to modify it. Otherwise, some orders will not belong to any customer--the integrity of the database is finished.
If the index integrity rule is applied to the table level, it is almost impossible to change the key of a record and all the associated records in the database without writing a large amount of code and attaching a deleted record. This process is often incorrectly clustered and should be avoided as much as possible.
Optional key (candidate key) sometimes can be the key
Remember, it's not the machine that queries the data, it's people.
If you have an optional key, you may further use it as the master key. In that case, you will have the ability to build a powerful index. This prevents people using the database from having to connect to the database to properly filter the data. On a database with strict control over the domain table, this load is more visible. If the optional key is really useful, it is the level of the primary key.
My view is that if you have an optional key, such as a state_code in a national table, you should not create a subsequent key on the unique key that cannot be changed. All you have to do is create worthless data. If you are using a table's subsequent key [alias] to establish an association with this table, the workload really needs to be considered.
Don't forget the foreign key.
The primary key fields that are automatically created by most database indexes. But don't forget to index foreign key fields, which are used every time you want to query the records in the primary table and their associated records. Also, do not index memo/notes fields and do not index large text fields (many characters), which will allow your index to occupy a large amount of database space.
Part 4-Ensuring data integrity
Enforcing data integrity with constraints rather than business rules
If you are dealing with requirements in accordance with business rules, then you should check the business hierarchy/user interface: If the business rules change later, then only need to update. If the requirements are rooted in the need to maintain data integrity, there is a need to impose constraints on the database level. If you do use constraints on the data tier, you have to make sure that you have a way to notify the user interface in a language that the user understands by not using the constraint check. The field name itself is not enough unless you have a lengthy field name.

Whenever possible, use a database system to achieve the integrity of your data. This includes not only the integrity of the implementation through standardization but also the functionality of the data. When writing data, you can also increase the trigger to ensure the correctness of the data. Do not rely on the business layer to ensure data integrity; it does not guarantee the integrity of the tables (foreign keys) so it cannot be imposed on other integrity rules.
Distributed Data System
For distributed systems, you should estimate the amount of data for the next 5 or 10 years before you decide whether to replicate all the data at each site or keep the data in one place. When you transfer data to another site, it's a good idea to set some tags in the database field. Update your tags after receiving your data at the destination site. For this kind of data transfer, write down your own batch or scheduler to run at specific intervals without allowing the user to transfer data after a daily job. Local copy of your maintenance data, such as the calculation of constants and interest rates, set the version number to ensure that the data at each site is exactly the same.
Force indication integrity (referential integrity?)
There is no good way to eliminate harmful data after it has entered the database, so you should remove it before it enters the database. Activates the indication integrity characteristics of the database system. This keeps the data clean and forces developers to devote more time to handling the error conditions.
Relationship
If there is a many-to-many relationship between two entities, and it is possible to convert to a many-to-many relationship, you might want to set up a many-to-many relationship in the first place. Shifting from the existing many-to-many relationship to many-to-many relationships is much more difficult than a many-to-many relationship in the beginning.
Take a View
To provide another layer of abstraction between your database and your application code, you can create a special view of your application without having to access the data table directly from the application. Doing so would also give you more freedom when dealing with database changes.
Make plans for data retention and recovery
Consider the data retention strategy and include it in the design process to proactively design your data recovery process. Use a data dictionary that can be published to users/developers to enable easy data recognition while ensuring the documentation of the data source. Write an online update to "update the query" to be able to process the update later in case of data loss.
Use stored procedures to make the system do heavy work
Having solved a lot of trouble to produce a highly-complete database solution, I decided to encapsulate some of the relational tables ' functional groups, providing a set of regular stored procedures to access each group to speed up and simplify the development of client code. A database is not just a place to store data, it is also a land of simplified coding.
Use Find
The best way to control data integrity is to limit the user's choice. Whenever possible, you should provide a clear list of values for the user to choose from. This will reduce the error and misunderstanding of the typing code while providing consistent data. Some public data is especially good for finding: Country code, status code, and so on.
Part 5th-Various tips
Documents, documents, documents
Documentation is documented for all shortcuts, naming conventions, restrictions, and functions.

Use database Tools that annotate tables, columns [fields], triggers, and so on. Yes, it's a bit of a bother, but in the long run it's very useful for development, support, and tracking changes.

Depending on the database system you are using, there may be some software that will give you some documentation for your quick start. You may want to start talking first, then get more and more details. Or you may want to schedule periodic presets, and then enter new data and detail each part as you progress. Whichever way you choose, always document your database, or create a document within the database itself or separately. In this way, when you go back to the 2nd version after more than a year, your chances of making a mistake will be greatly reduced.
Use common English (or any other language) rather than coding
Why do we often use codes (such as 9935A may be the "Tsingtao Beer" supply Code, 4XF788-Q may be the account encoding)? There are many reasons. But users often think in English instead of coding. The accountant who has worked for 5 years may know what 4xf788-q is, but the new one may not necessarily be. It is best to sort by English name when creating Drop-down menus, lists, reports. If you need to code, then you can attach the code next to the user knows English.
Save Common Information
It is useful to have a table that stores general database information specifically. I often hold the current version of the database, the most recent check/fix (for FoxPro), the name of the associated design document, the customer, and so on. This enables a simple mechanism to track the database, which is especially useful for non-client/server environments when customers complain that their database is not meeting the desired requirements.
Testing, testing, repeated testing
After you have established or revised a database, you must test the data field with the new data entered by the user. Most importantly, let the user test and work with the user to ensure that the data type you choose meets the business requirements. Testing needs to be done before the new database is put into actual service.
Check Design
A common technique for checking database design during development is to check the database through its supported application prototypes. In other words, the prototype application for each final expression data ensures that you examine the data model and see how to get the data out.
Microsoft Visual FoxPro Design Tips
For complex Microsoft Visual FoxPro database applications, you can put all the primary tables in one database container file, then add additional database table files and load special files related to the original database. Use these files to connect to the primary table in the primary file as needed. such as data input, data indexing, statistical analysis, to management or government departments to provide reports and all kinds of read-only queries. This approach simplifies the allocation of user and group permissions and facilitates the grouping and partitioning of application functions (stored procedures) that are easy to manage when the program must be modified.