SQL database design experience

2009-03-21 16:47


SQL database design experience

A successful management system is: [50% + 50% of business software] formed, and 50% of the successful software and [25% database + 25% of the procedures] which consists of the database design good or bad is a key. If the business life of the data necessary to compare the blood, then the application of database design is the most important part. Materials related to the voluminous database design, university degree courses there is also a special on. However, as we have repeatedly emphasized, even the best teachers but also the teachings of experience. Summed up over the years so I go by the tortuous path and experience, and online database to find some very accomplished design professionals to design the database to teach everyone the skills and experience. Select the best one of the 60 skills, and put these skills in the preparation of this article has become, in order to facilitate the indexing of its content is divided into five parts:
"Part 1 - Prior to this part of the design of the database listed 12 basic skills, including naming convention and a clear business needs.
"Part 2 - Design database tables Guide a total of 24 skills, covering the design of the table field and should be avoided, such as frequently asked questions.
"Part 3 - key select key then select how? Here are 10 skills specifically related to system-generated primary key of the right to use, also when and how to index fields to obtain the best performance.
"Part 4 - to discuss how to ensure data integrity to maintain a clear and robust database, how to put harmful to minimize the extent of the data.
"Part 5 - a variety of tips are not included in the above section 4 of the other techniques, all kinds, with their hope that the database development work you will relax some more.
Part 1 - Design the database before
1. Study the existing environment in the design of a new database, you should not only be carefully studied business needs but also to study the existing system. Most of the database project is not set up the start from scratch; Typically, institutions will always exist to meet the specific needs of the existing system (which may not achieve automatic calculation). It is clear that the existing system is not perfect, otherwise you do not have to set up a new system. But the old system you can probably find some minor problem will be ignored. In general, the study of the existing system is absolutely good for you.
2. The definition of the standard naming convention of the target database objects must be defined naming convention. Of the database table, the one from the project should begin to determine table name is plural or singular form. In addition to the definition of an alias to the table easy rules (for example, if the table name is a word, check word alias on the first four letters; if the table were two words, two words on the merits of the first two letters composed of four letters long alias; first name if the table composed by three words, you may wish to start from the two words in a pleasing then a word from the last two letters out again, the effect is composed of 4 letters long aliases, and the rest followed on and so forth) on the work table, the table name with prefix WORK_ can be appended to the table using the name of the application. Table column [field] to address the use of a set of key design rules. For instance, if the key is the type of figure, you can use as a suffix _N; If it is the type of characters can be used suffix _C. The list of [field] who should be the standard prefix and suffix. Again, if you have a lot of the table "money" field, you may wish to give each column [field] to add a suffix _M. Also, the date column [field] is best to name the head of D_.

Checklist names, statements and inquiries were named among the norms. You will probably soon be a database of these different elements of the name confused me. If you insist on a unified database to name these different elements, at least you should be at the beginning of these objects with the name of Table, Query or Report, such as the prefix to be distinguished.

If using Microsoft Access, you can use qry, rpt, tbl and mod symbols to identify objects (such as tbl_Employees). And SQL Server at my time of dealing with tbl also used to index table, but I sp_company (now sp_feft_) logo stored procedure, because at some time if I found a better way to deal with tend to save some good copy. I realize at the time SQL Server 2000 with udf_ (or similar tag) identification of the function to prepare me.
3.工欲善其事, prerequisite uses its ideal database design tools, such as: SyBase company PowerDesign, she supported the PB, VB, Delphe such as language, can be connected through ODBC on the market more than 30 popular databases, including dBase, FoxPro, VFP, SQL Server and so on, the future I will have the opportunity to focus on the use of PowerDesign.
4. Access to data resources in manual mode
Are looking for examples of patterns of people can read, "Data Model Resource Book", a book by Len Silverston, WH Inmon and Kent Graziano prepared, is worth having one of the best data modeling books. The book includes chapters covering a wide range of data fields, such as personnel, and other agencies and job performance. Other you can also refer to: [1] Xuan Wang Shan Sa division of An Introduction to Database Systems (Second Edition) Higher Education Press in 1991, [2] [United States] Steven M. Bobrowski with Oracle 7 and Client / server computing technology from entry to the master, such as translation of Liu Yuan Electronics Industry Press, 1996, [3] Zhou Zhongyuan Information System Modeling Method (below) of Electronics and Information 1999 3, 1999
5. The future of the Imagination, but not forgotten the lessons of the past, I found that asking the user how to view changes in future demand for very useful. This will serve two purposes: First of all, you can clearly know at which place the application of the design should be more flexible and how to avoid performance bottlenecks; Second, you do not know in advance to determine the demand for change and users will be surprised as you .

We must remember the lessons of the past! We should also be adopted by developers to share their insights and experiences and help each other. Even if users do not need to consider what they would no longer supported, we should also in this respect to their education, we have faced such a moment of "Had a nice to do so ..."
6. At the physical practice of logic design before the physical design of in-depth before the first logical design. With many CASE tools have emerged out of design you can achieve a very high standard of logic, you can often be a whole database on a better understanding of all aspects of the design required.
7. You know the business you determine the full system from the perspective of customers not to meet their needs before you at the ER (entity relationship) model even add a data table (how, you do not have model? Skills that you see 9). You know the business can be developed in subsequent stages of the substantial time savings. Once you clear the business needs, you can make a lot of decision-making.

Once you think you have defined the business, you'd better carry out a system with the client communicate. Customers and the terms used to explain to them what you think and you have heard. At the same time should also be possible to use, it will have to wait and vocabulary to express the relationship between the base of the system. So you can let your customers correct understanding of yourself and then do a good job in the next step of the ER design.
8. To create ER diagrams and data dictionary must take the time to want to create ER diagrams and data dictionary. Should contain at least one of the data type of each field in each table and the main foreign key. Create ER diagrams and data dictionary is indeed a time-consuming but other developers want to know the whole design is absolutely necessary. The sooner the more it can help to avoid the creation of the future, facing the possible confusion, so that we can let any person know the database have made it clear how access to data from the database.

ER has a chart such as the latest documentation, such as how to emphasize its importance can not be overemphasized, and this shows that the relationship between the useful forms, and data dictionary is illustrated in the use of each field as well as any aliases that may exist. Documents on the SQL expression of this is absolutely necessary.
9. Create a model of a chart is worth a thousand words: the development of not only want to read and realize it, but also use it to help themselves and user dialogue. Mode of collaboration will help improve performance, so that at an early design of the database is almost impossible to major problem. Models do not have to get very complicated; even easy to hand-written on a piece of paper on it. Just want to ensure that the logic of their relationship on the future benefits.
10. From the definition of input and output to gain the upper hand at the demand for database tables and fields (input), the first to check or the Report has been designed, query and view (output) in order to support these outputs in order to decide what is necessary to form and field. Here is a simple example: If a customer required to sort statements in accordance with the zip code, sub-and sum, to ensure that you include one of the field instead of a separate zip code to zip code into the address field in糅.
11. Report skills to understand how users are often reporting data: Report submitted by batch or online? Time interval is daily, weekly, monthly, quarterly or annually? If necessary also to consider the creation of summary tables. System-generated primary key it is difficult to manage in the Report. Users with system-generated primary key of the table with Vice-key search will often return a number of duplicate data. This relatively low performance of the retrieval and easy to cause confusion.
12. To understand customer needs
This should be a clear look, but the demand is from customers (in this case internal and external customers from the point of view). Not to rely on the user needs to write it down, the real needs of customers at the head. Customers want you to explain their needs, and along with the continued development, but also often asked to ensure that their customers demand is still in the development of the purpose. Truth is a constant: "I saw my only know what I want" will inevitably lead to a lot of rework, because the database did not meet customer never needs to write down the standard. To make matters worse and you needs to explain to them belong to only yourself, but may be completely wrong.
Part 2 - Design tables and fields
1. Examine the various changes in my time in the design of the database will be taken into account which data fields will be possible to change the future. For instance, the surname is the case (Note that the name is the West, such as women from the husband's name after getting married, etc.). Therefore, customers in the information storage system set up, I tend to separate at a data storage table name field, but also the start and termination of additional fields such as date, so you can track changes in the data entry.
2. The use of meaningful field names once I take part in the development of a project, one of the other programmers have inherited from the process, the programmers like to use the instructions on the screen display data field name terms, which do not depend on, but unfortunately , she also likes to use some strange nomenclature, the name of Hungary adopted the name and control number of combinations, such as cbo1, txt2, txt2_b and so on.
Unless you use only your initials for the field names of the system, or you try to field more clearly described. Of course, the other did the first, such as Customer_Shipping_Address_Street_Line_1, although very rich illustrative, but not so long people are willing to type the name of the specific measure at your grasp on the Medium.
3. The use of the prefix table name if there is a lot more than the same type of field (such as FirstName), you may wish to use a specific form of the prefix (such as CusLastName) to help you ID field.

Time-sensitive data should include the "Recently Updated Date / Time" field. Time tag data to search the cause of the problem, according to the date of re-treatment / removal of heavy-duty data and old data were particularly useful.
4. Standardization and the standardization of data-driven data is not only convenient but also to facilitate their own other people. For example, the user interface if you want access to external data sources (documents, XML documents, other databases, etc.), you may wish to put the corresponding connection and path information stored in the user interface to support the exterior. Also, if the user interface such as the implementation of workflow tasks (send e-mail, print letterheads, modify the record, etc.), then the job streams generated data can also be stored in the database. Pre-arranged the total effort required, but if these processes using data-driven approach rather than a hard-coded, then the strategy will change and maintenance easier. In fact, if the process is data-driven, you can put considerable blame to the user, by the user to maintain their own workflow process.
5. Standardization should not over for those who are not familiar with the term standardization (normalization) of the person concerned, the standardization of the table to ensure that the field is the most basic elements, which measures contribute to the elimination of redundant data in the database. There are several forms of standardization, but the Third Normal Form (3NF) is generally considered in terms of performance, scalability and data integrity to achieve the best balance. To put it simply, 3NF requirements:
* Each table value can only be an expression.
* Each line in the table should be the only logo (there is only button).
* Paper should not be dependent on other key storage of non-key information.
3NF database comply with the standard have the following characteristics: there is a group of specialized storage table to link the adoption of key interrelated data. For example, a store and its customers relating to orders may 3NF database has two tables: Customer and Order. Order Form Order does not contain information related to any customer, but the table will be a key store, the key point to Customer table contains the customer information line.
A higher level of standardization also has, but whether the standards must better? The answer is not necessarily. In fact, for some projects, but even the introduction of 3NF database can be too high complexity.

For efficiency's sake, do not standardize on the table is also necessary in some cases, a lot of such examples. Once there is a food and beverage analysis software development is the use of live non-standardized form to query the time from an average of 40 seconds down to about two seconds. Although I have to do this, but I certainly do not put the data table as a non-standardized design course. However, the specific operation is a kind of derivative. So if the problem re-presentation of non-standardized form is possible.
6. Microsoft Visual FoxPro Report skills If you are using Microsoft Visual FoxPro, you can use on the field of user-friendly names instead of numbers: such as Customer Name instead of using txtCNaM. In this way, when you use the wizard [Wizards, the people of Taiwan as the 'Wizard'] to create forms and reports, the names of programmers who do not make it easier for people to read.
7. Is not active or do not use the indicator
Where the addition of a field that records are no longer active in the business quite useful. Whether customers, employees or any other person, it can help to re-run the query when the filter status active or inactive. At the same time also eliminates the use of new user data at some of the problems faced by, for example, some records may no longer be used for them, and then delete when prevention can play a role.
8. The use of the definition of the role of entities belonging to certain categories of out [field]
The need to belong to specific categories or with a specific role in the definition of things to do, the role of entities can be used to create a specific time-dependent relationship, so that we can achieve self-documentation.
The meaning here is not so PERSON entity with Title field, but why do not PERSON entities and entities to describe PERSON_TYPE well? For example, when John Smith, Engineer upgraded to John Smith, Director and even the final climb John Smith, CIO of the high, and all you do is change the PERSON and two tables PERSON_TYPE the relationship between the keys, while an additional Date / Time field to know when change is occurring. In this way, your PERSON_TYPE table PERSON includes all the possible types, such as Associate, Engineer, Director, CIO or CEO and so on.
Alternative is to have a change PERSON title records to reflect the new changes, but at the time so can not track individuals on the location of a specific time.
9. The use of common data entities, naming institutions Organize data using the simplest solution is commonly used first name, such as: PERSON, ORGANIZATION, ADDRESS and PHONE and so on. When you put the names of those commonly used in general or combined to create specific entities corresponding vice, you have been using the special version of their own. The beginning of the main reasons for using general terms all of the specific user is able to abstract and concrete things.
With these abstract that you can at the first two marking the particular use of their name, such as, PERSON is probably Employee, Spouse, Patient, Client, Customer, Vendor, etc. or Teacher. The same, ORGANIZATION may be MyCompany, MyDepartment, Competitor, Hospital, Warehouse, Government and so on. Finally ADDRESS specifically for the Site, Location, Home, Work, Client, Vendor, Corporate, and so on FieldOffice.
Abstract general term used to identify "things" will allow you the type of data in the associated requirements to meet the needs of the business was enormous flexibility to do so can significantly reduce the data storage capacity required for redundancy.
10. Users from all over the world used in the design of network characteristics, or with other international database, we must remember that most countries have different field formats, such as zip code, and some countries, such as New Zealand there is no postal code said one.
11. Duplicate data required the use of the separation of the data sheet if you find yourself repeating the input data, please create a new table and the new relationship.
12. Each table should be useful to add the three fields
* DRecordCreationDate, under the default in VB is Now (), and in SQL Server under the default GETDATE ()
* SRecordCreator, under the SQL Server defaults to NOT NULL DEFAULT USER
* NRecordVersion, the version of Record marker; contribute to accurate description of the data records appear null or the reasons for missing data
13. On the address and phone number of fields used to describe a short span of his street address on record is not enough. Address_Line1, Address_Line2 and Address_Line3 can provide greater flexibility. Also, phone numbers and e-mail address of the best have their own data sheet, which has its own marker types and categories.

Standardization happen too careful to do so may lead to performance problems. Although the address and telephone number of tables can usually achieve the best separation of status, but if you often visit this type of information required, may be stored at the table his father, "preferred" information (such as Customer, etc.) some more appropriate. Non-standardized and accelerated access to the compromise between the must have significance.
14. Using more than the name of the field I am very surprised that many people in the database to remain a given field name. I think there is only just getting started before developers do, but in fact this practice is very common online. I suggest that should be regarded as both first and last name field to deal with, and then query at a time when they are together again.

ME is the most commonly used at the same table to create a calculated column [field], which can automatically connect the field after the standardization of such data when it changes, then change. However, this modeling software used to do at the time it got smart. In short, the way used to connect field can effectively isolate the user interface applications and developers.
15. Beware of the object of case mix and special characters
What annoyed me the past is one thing there is a database object of a mix of upper and lower case, such as CustomerData. This problem to the Oracle database from Access exist. I do not like to mix this case the object naming method, the results also have to manually modify the name. Think about this database / application to mix the use of more powerful database that day吗? Make use of all capital letters and includes the name of the next designated site better readability (CUSTOMER_DATA), definitely not to the object name in the left spaces between characters.
16. Careful to retain the word you want to ensure that the field names and to retain the word NOT, database systems or methods used to visit the conflict, for example, I recently prepared an ODBC connection program has a table, use one of the DESC field names as an illustration . Consequences can be imagined! DESC is DESCEN * to retain the word after the abbreviation. Exterior of a SELECT * statement it can be, but I got a lot of it is useless information.
17. To maintain field names and types of consistency in the name field and its time to specify the data type must to ensure consistency. If the field in a table called "agreement_number", you do not put in another table into a first name "ref1". If the data type is an integer in a table, it may not in another table into the character. Remember, you finish their life, other people have to do with your database.
18. Carefully select the type of figure used in the SQL type smallint and tinyint to be especially careful, for example, if you want to look at the total sales month, the total amount of your field type is smallint, then, if the total amount of more than $ 32767 you can not be calculated operation of the.
19. Delete tag in the table contains a "Delete Marked" field, it can be put on the line marked for deletion. Relations at a separate database not to delete a line; the best procedures used to clear the data but also to carefully safeguard the overall index.
20. To avoid the use of the functions of flip-flop flip-flop can usually achieve by other means. Flip-flop in the debugging process could be interference. If you do need the use of flip-flop, you'd better concentrate on its documentation.
21. You contain the proposed version of the mechanism introduced in the database version control mechanism to determine the version of the database. Anyway you have to realize this requirement. Over time, the needs of the users will always be change. Eventually may be required to modify the database structure. Although you can check for new fields or index to determine the version of the database structure, but I find the version information stored directly to the database more convenient does not it? .
22. Give enough margin to stay the text field
ID type of the text field, such as customer ID or order number and so on should be set greater than generally imagined, because you will not be long because most of them will want to add extra characters and very embarrassing. For example, assume that your 10-digit customer ID for long. That you should put the length of the database table field is set to 12 or 13 characters long. Can this be a waste of space? There is a point, but not so many of you to imagine: a 3-character field longer one has at one million records, coupled with the index case that was only to occupy the entire database of more than 3MB of space. However, this additional space occupied has no future reconstruction of the entire database can be achieved on the scale of growth of the database. ID card number from the 15 into 18 is the best and most painful example.
23. Out [field] naming techniques we have found that if you give each table column [field] who have adopted a unified prefix, then the SQL expression in the preparation time will be greatly simplified. Do indeed have shortcomings, such as automatic table damage the role of connectivity tools, which put the public out [field] were linked with some of the database, but sometimes even these tools do not have connection error. Here is a simple example, suppose there is two tables:
Customer and Order. Customer table is the prefix cu_, so the list of sub-paragraph as follows: cu_name_id, cu_surname, cu_initials and so on cu_address. Order table prefix is or_, so sub-paragraph are:
or_order_id, or_cust_name_id, or_quantity and so on or_description.
Selected from the database so all the data can be written in the SQL statement as follows:
Select * From Customer, Order Where cu_surname = "MYNAME";
and cu_name_id = or_cust_name_id and or_quantity = 1
In the absence of these prefixes are written in circumstances like this (using an alias to distinguish between):
Select * From Customer, Order Where Customer.surname = "MYNAME";
and Customer.name_id = Order.cust_name_id and Order.quantity = 1
The first one SQL statement, type the number of characters not less. However, if the query involves five or more columns in table [field] you know this technique has been useful to many.
Part 3 - select key and index
1. Data mining to pre-planned
I was once a department to deal with customers more than 80,000 copies of Contacts at the same time fill in the necessary data for each client (this is not a small live). I would also like to determine from a group of customers as the market aim. When I start from the design table and field, I try to not increase too much the main index of the field in order to expedite the speed of the database. And I realized that a particular group of information inquiries and the speed of extraction is neither accurate nor quick. The results had no choice but to rebuild the main index and consolidation of data fields. I found the instructions there is a quite critical - when I want to create a search when the type of system to be used why the index number as the main field it? I can search by fax number, but it is almost like the same type of system is not important to me. Using the latter as the main field, the database update on the re-indexing and retrieval more quickly.

Operational data warehouse (ODS) and data warehousing (DW) of these two conditions have the data index is different. In the DW environment, you have to consider is how to organize sales department sales activities. They are not database administrators, but they identified the key information table. Here the designer or database staff database structure should be analyzed to determine the performance and output between the right of the best conditions.
2. The use of system-generated primary key of one of these with the skills, but I think there is a need to repeat them here to remind everyone. If you are always in the design of the database when using system-generated key as the primary key, then you de facto control of the integrity of the database index. In this way, databases and非人工effective control mechanism to store data in each line of the visit.
The use of system-generated key as a primary key advantages: When you have the same key structure of the logical flaw is easy to find.
3. Decomposition index field is used for separation of naming fields and contains user-defined fields to support the statements, consider the decomposition of other fields (or even the primary key) of its constituent elements so that users can index them. The index will speed up the Report Generator and SQL script execution. For example, I usually have to use in SQL LIKE expression to create the Report, as case number field can not be broken down into year, serial number, case type and such factors as defendant code. Performance will deteriorate. If the year and type of field can be decomposed into the index field then these statements will be up and running more quickly.
4. 4 key design principles
* To create a foreign key related fields.
* All the keys have to be Unique.
* Avoid the use of composite keys.
* Unique foreign key is always associated key field.
5. Do not forget to index the index is to obtain data from the database of one of the most efficient manner. 95% of database performance problems can be solved using indexing technology. As a rule, I usually use the primary key to the logic of the only group index, keys on the system (as stored procedures) using only non-group index, for any foreign key column [field] the use of non-index group. However, the index is like salt, too much food on the salty. You have to consider how much space a database table to visit, whether or not these visits have mainly used for reading and writing.

Most of the database are automatically created the index primary key field, but do not forget the foreign key index, which is frequently used keys, such as running queries show the main table and all associated records in a table on the useful. Also, not to index memo / note field, not to index a large field (there is a lot of characters), so the index will be taking up too much storage space.
6. Not to index a small table used for small data tables not to set any keys, if they always have to insert and delete operations do not even made. Of these insert and delete operations to maintain the index table space scan may consume more time.
7. Not to put Social Security number (SSN) or ID card number (ID) is always selected as a key or not to use the SSN as a database key ID. In addition to privacy reasons, the Government must not allow more and more tend to put SSN or ID used in addition to other income-related purpose, SSN or ID required manual input. Never use the manual input of the key as primary key, because once you enter the wrong, only you can do is delete the entire record and then start all over again.

I break the procedure to others, I saw a lot of people also put SSN or ID serial number has been used to do, of course, even though doing so is illegal. But people also know that this is illegal, but they have got used to it. Later, with the identity theft crimes increase, my colleagues are now suffering from a data structure of an organization SSN or ID to delete.
8. Do not use the user's key in determining what the key field as the time table may be user must be careful to edit the field. Under normal conditions the user can select not to the field as the Edit button. This will force you to do both to take the following measures:
* Create a record in the Edit field on the behavior of the user restrictions. If you do, you may find your application at the sudden change in business needs, and users need to edit non-editable field that the lack of enough flexibility. When users enter data until after the record-keeping system only to find out how they want to question? Delete reconstruction? Whether or not the reconstruction of Record if users can not Go Away?
* Detect and correct some key conflict. Typically, the fees to get spots on it, but from a performance point of view on the cost of doing so on a relatively large. Also, the correct key you may be forced to break through your data and business / user interface between the isolation layer.
Or so the old saying again: you want the design to adapt to the user and not allow users to adapt to your design.

Not have to update the primary key of the reason is that the relationship between the model, the primary key to achieve a link between different forms. For instance, Customer table has a primary key CustomerID, and customer orders are stored in another table. Order forms may be the primary key OrderNo or OrderNo, CustomerID and date combination. No matter which button you select settings, you need to store the table in the Order to ensure that you can CustomerID to placing orders for customers to find their records.
If you changed at Customer exterior CustomerID, then you must find all the Order table modify records. Otherwise, some orders will not belong to any client - even if the integrity of the database finished.
If the integrity of the rules imposed by the index into the table level, then do not prepare a large number of additional code and delete the case record is almost impossible to change a record in a key and all the associated database records. This process is often wrong and so should try to avoid problems.
9. Optional key (candidate key) the master key to remember sometimes, query data are not machines but people.
If you have an optional key, you put it further possible to use the master key. In that case, you will have the ability to set up a powerful index. This will prevent the use of the database has to connect people in order to properly filter database data. Strict control of the domain table in the database, this load is more eye-catching. If the optional key really useful, and that is the main key to achieve the standard.
My view is that if you have an optional key, such as the National Paper state_code, not to you should not change the existing key on the only button to create the follow-up. You do nothing but create no value data. Since you such as excessive use of follow-up key in table [alias] set up this table related to the operation load really need to consider about the whole thing.
10. Do not forget the majority of the foreign key index automatically creates a database of the primary key field. But do not forget to index the foreign key field, you want to query in the main table records and its associated records will be used each time. Also, not to index memo / notes field but not to index a large text field (many characters), so you will make many of the index database to occupy space.
Part 4 - to ensure the integrity of the data
1. With restraint rather than mandatory data integrity business rules, if you deal with in accordance with the needs of business rules, then you should check the level of business / user interface: the future if business rules change, then you only need to be updated. If the demand stems from the need to maintain data integrity, then the level in the database on the conditions necessary to impose restrictions. If you really used in the data binding layer, you have the means to ensure that the update can not be bound to check the reasons for users to understand the language used to inform the user interface. Unless you rename the field very long, or field name itself is not enough.

Whenever possible, please use the database system data integrity. This will not only achieve through standardization but also the integrity of the functional data. Writing at a time when the data can also increase the trigger to ensure the accuracy of the data. Not to depend on the business layer to ensure data integrity; it can not guarantee that forms between the (foreign key) so the integrity of the integrity of the rules should not impose on others.
2. Distributed Data System of Distributed System, in deciding whether or not you at all sites or all data copy the data stored in a place should be estimated before a look at the future 5 years or 10 years the amount of data. When you put data to other sites, the best field in the database set up some markers. The purpose of the site at the data received after you update your tags. For such data transmission, please write your own batch scheduler or a specific time interval to run every day and not let users transfer data after the job. To maintain local copies of your data, such as calculation of constant and interest rates, set the version number at each site to ensure that data are fully consistent.
3. Coercive instructions integrity (referential integrity?)
NOT a good way to enter data in the database after harmful to eliminate it, so you should be at it before they enter the database removed. The instructions to activate the integrity of the database system characteristics. This data can be maintained clean and will force developers to devote more time to deal with error conditions.
4. The relationship between
If two many-to-one relationship between entities, and probably also into a many-to-many relationship, then the best one you set to start on the many-to-many relationship. Many-to-one relationship from the existing many-to-many relationship into one that is beginning to be much more difficult many-to-many relationship.
5. For the use of view in your database and your application code to provide another layer of abstraction, you can set up your application view specific applications without having to go direct access to data sheets. Does this also mean that changes in the treatment database to provide you more freedom.
6. To maintain and restore the data to develop plans to consider strategies to maintain data and included in the design process, you pre-designed data recovery process. Used can be published to the users / developers to facilitate the implementation of the data dictionary to identify the data at the same time to ensure that the data of the source document. The preparation of online update to "update query" for the event of data loss after the treatment can be re-updated.
7. To use the system stored procedure to do a lot of hard work to resolve the trouble to produce a high degree of integrity of a database solution, I have decided to package some of the functional group linked table, provide a set routine to access the stored procedure in order to speed up in each group speed and simplify development of client code. Database is not only a place of storage of data, it is to simplify the coding.
8. The use of search control data integrity is the best way to limit the user's selection. Should be possible as long as there is available to the user a clear choice for the value of the list. This type of code will reduce errors and misunderstandings while providing data consistency. Particularly suitable for certain public information search: country code, such as status code.
Part 5 - a variety of small skills
1. Document, document, document all the shortcuts, named norms, constraints and functions should be the preparation of the document.

Used to the table, column [field], triggers and other database tools add comments. Yes, This is a bit cumbersome, but in the long run, it would not do the development, support and follow-up of a very useful modification.

Depends on the database system you use, there is probably some software will give you a number for you soon-to-use document. You may want to start talking first, and then get more and more details. Or you may wish to pre-scheduled periodic, enter new data at the same time, as you progress on the details of each part. No matter which way you choose, you must be a database of documents of, or in the database or its own internal set up a separate document. In this way, when you have more than a year and then turn around and do the first two versions of the opportunity to make mistakes you will be greatly reduced.
2. The use of common English (or any other language) and why not to use our code often used encoding (such as 9935A may be 'Tsingtao Brewery' supply code, 4XF788-Q may be encoded accounts)? There are many reasons. However, users usually think in English rather than code. 5 years of accounting job may know what is 4XF788-Q, but may not necessarily new to the. In the creation of the drop-down menu, a list of the best statements in accordance with the sort of English. If you need to encode the next code you can attach a user to know English.
3. Save commonly used information to make a table devoted to information storage in general a very useful database. I often stored in the database table the current version, the latest inspection / repair (of FoxPro), the name associated design documents, and other customer information. This simple mechanism to track implementation of a database, when customers complained that their database does not achieve the desired requirements and contact you when to do so for non-client / server environment is particularly useful.
4. Testing, testing, repeated testing to establish or amend the database, users must use the new data entered field test data. Above all, enabling users to test and to work together with customers to ensure that you select the type of data to meet business requirements. Testing required in the new database prior to the completion of the actual services.
5. Designed to check during the inspection in the development of a common database design and technology are supported through the application of its prototype to check the database. In other words, for each of the final prototype of the application of expression data to ensure that you check the data model and how to remove the data view.
6. Microsoft Visual FoxPro design skills to complex Microsoft Visual FoxPro database application, we can put all of the main table in a database container file, and then add other database table file and loading the database with the original documents relating to the special. According to these documents need to connect to the main document of the main table. Such as data entry, data indexing, statistical analysis, to management or to provide statements of government departments as well as read-only queries, and so on. The measures to streamline the user and group permissions of the distribution, but also conducive to the application function (stored procedure) and divided into groups, which must be changed at a time when the procedure easy to manage.