Problems to be considered in designing a database

Successful management system =50% Business + (25% of database +25% programs)

1, investigate the existing system environment
Most database projects are not built from scratch, and there is always an existing system in the organization to meet specific needs. 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 discover subtle problems that may be overlooked. In general, it is absolutely good for you to examine the existing system.

2, fully anticipate the trend of the upgrade of demand
Asking the user how to look at future changes in demand can be useful for two purposes: first, you can clearly see where the application design should be more flexible and how to avoid performance bottlenecks, and second, users will be as surprised as you are when there is no predetermined change in requirements.

3, fully understand the needs of customers
It should seem obvious, but the demand is from the customer (here, from the perspective of internal and external customers). Do not rely on the user write down the demand, the real demand in the customer's head. You have to ask your customers to explain their needs, and as development continues, ask customers to ensure that their needs are still being developed.
Once you think you've identified your business, you'd better have a systematic communication with your customers. Use the customer's terminology and explain to them what you think and what you hear. The relationship base of the system should also be expressed in terms of possible, will and must. This way you can get your customers to correct your own understanding.
Understanding the business can save a lot of time in the future development phase, and you will find that once you have identified the business requirements, you can make many decisions on your own.

4. Determine the naming specification for data Objects
Be sure to define the naming conventions for database objects, and consider using a well-defined prefix or suffix:
For the view, the VW_ prefix can be used;
For tables, table names can be prefixed with tbl_;
For the column in the table [field], the number type can use _n as the suffix, the character type can use the _c suffix, etc., the money type may use the _m suffix, the date type may use _d as the suffix and so on;
For stored procedures, the SP_ prefix can be used;
For custom functions, you can consider using the udf_ prefix;
In addition, all uppercase letters are used, and the following lines divide words, such as: Tbl_customer_info

5. Create a data dictionary
Be sure to take the time to create a data dictionary that should contain at least the data type of each field and the primary foreign key within each table. Creating a data dictionary is a bit time-consuming but it is absolutely necessary for other developers to understand the entire design. The sooner you create, the more you can help avoid possible confusion in the future, so that anyone who understands the database knows exactly how to get data from the database.

6, from the input and output
When defining database table and field requirements (input), you should first examine existing or already designed reports, queries, and Views (output) to determine which tables and fields are necessary to support these outputs. To give a simple example: if a customer needs a report sorted, segmented, and summed according to the ZIP code, you have to make sure that you include a separate ZIP code field instead of melted the ZIP code into the Address field.

7. Reporting Skills
To understand how users typically report data: batch or submit a report online? 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 a report. A user retrieving a secondary key in a table with a system-generated primary key often returns many duplicate data. This kind of retrieval performance is relatively low and easily cause confusion.

8. Consider internationalization issues
When designing a database that uses networks or other international features, it is important 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.

9, recorded version
     drawing on Oracle's design patterns, each table sets the following 6 fields:
     Create_date
    last_modify_date
    creator
     modifier
    modify_number
    is_deleted
contains a The delete tag field so that the row can be marked for deletion. Do not delete a single row in a relational database, preferably with a clear data program and carefully maintain the integrity of the index.
 
10, carefully select the data type
     use smallint and tinyint types in SQL to be especially careful, for example, If you want to look at the monthly sales totals, your total field type is smallint, then if the total amount exceeds the $32,767 you will not be able to do the calculation.
     Be sure to ensure consistency when naming fields and specifying data types for them. If the field is called "Agreement_number" in a table, you should not change the name to "Ref1" in the other list. If the data type is an integer in one table, it will not become a character type in the other table. Remember, you have done your job, and others will use your database.
 
11. The ability to avoid using triggers
     triggers can often be implemented in other ways. Triggers can become interference when the program is debugged. If you do need to use a trigger, you'd better focus on documenting it.

12. Sufficient margin for text field
The ID type of the text field, such as customer ID or order number should be set more than the general imagination, because the time is not long you will probably be to add extra characters and embarrassed. Let's say that your customer ID is 10 digits long. Then you should set the database table field length to 12 or 13 characters long. Is this a waste of space? It's a little bit, but it's not as much as you think: a field with a length of 3 characters in the case of 1 million records, plus a little index, but the entire database occupies more than 3MB of space. But this extra space will increase the size of the database without having to refactor the entire database in the future. The number of ID cards changed from 15 to 18 is the best and most painful example.

13. Enforce 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 level/user interface: If the business rules change later, you only need to update them. If demand is a result of the need to maintain data integrity, then there is a need to impose constraints on the database level. If you do adopt a constraint on the data layer, you have to ensure that you have a way to notify the user interface in a language that the user understands because the update does not pass the constraint check. Unless your field name is lengthy, the field names themselves are not enough.
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 table (foreign key), so it cannot be imposed on other integrity rules.

14. Distributed Data System
For distributed systems, you should estimate the amount of data for the next 5 years or 10 years before you decide whether to replicate all the data at each site or save it in one place. When you transfer data to other sites, 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 a specific time interval instead of letting the user transfer data after a day's work. Copy your maintenance data locally, such as calculation constants and interest rates, and set the version number to ensure that the data is fully consistent at each site.

15. Mandatory indication integrity (referential integrity?)
There is no good way to eliminate the harmful data after it has entered the database, so you should remove it before it enters the database. Activates the indicator integrity attribute of the database system. This keeps the data clean and forces developers to devote more time to handling the error conditions.

16. Relationship
If there is a many-to-one relationship between two entities, and it is possible to convert to many-to-many relationships, you might want to set up a many-to-many relationship at the outset. The transition from the existing many-to-one relationship to a many-to-many relationship is much more difficult than the first is many-to-many relationships.

17. Adopt View
To provide another layer of abstraction between your database and your application code, you can create a dedicated view of your application without having to access the data tables directly from the application. Doing so also gives you more freedom when dealing with database changes.

18, do not 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 the logical primary key, a unique non-group 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 consider how large the database is, how the table is accessed, and whether the access is primarily used for reading and writing.
Most databases index the auto-created primary key fields, but don't forget the index foreign keys, which are also frequently used keys, such as running a query to display the main table and a record of all associated tables. Also, do not index the Memo/note field, do not index large fields (there are many characters), which makes the index occupy too much storage space.

19, using stored procedures to make the system do heavy work
After solving a lot of trouble to produce a highly-integrated database solution, I decided to encapsulate the functional groups of some associated tables, providing a complete set of regular stored procedures to access the groups to speed up and simplify the development of the client code. The database is not just a place to store data, it is also a simplified code location.

20. Using the system-generated primary key
If you are always designing a database with a system-generated key as the primary key, you actually control the database's index integrity. This allows the database and non-manual mechanisms to effectively control access to each row in the stored data. There is also an advantage to using a system-generated key as a primary key: it is easy to find a logical flaw when you have a consistent key structure.

Problems to be considered in designing a database