Experience of database design (change)

2010-04-03  来源:本站原创  分类:Database  人气:335 

A successful management system is: [50% + 50% of business software] is composed, while another 50% of the successful software [25% database + 25% of the programs] are composed of good and bad database design is a key. If you compared the life of the enterprise data necessary for the blood, then the database design is the most important part of the application. Voluminous material related to database design, university degree courses where there are special about. However, as we have repeatedly emphasized, even the best teachers can match the experience of teaching. So I summed up the detour to go over the years and experience, and on the Internet to find some of the database design professionals versed in some of the design database to teach you the skills and experience. 60 of them selected the best skills, and compiled an article of these techniques, in order to facilitate the indexing of its content is divided into five parts:

Part 1 - Design the database before

This section lists the 12 basic skills, including naming and clear business requirements.

Part 2 - Design database tables

Guidelines of a total of 24 skills, covering the table field design and should avoid the common problems.

Part 3 - Selection key

Select key then how? Here are 10 tips specifically related to system-generated primary key of the correct usage, as well as when and how to get the best performance index fields so.

Part 4 - To ensure data integrity

Discuss how to maintain a clear and robust database, how to minimize harmful data.

Part 5 - Various Tips

Not included in the above four parts of the other techniques, variety, with their hope that the development of your database will be more relaxed.

Part 1 - Design the database before

Existing environmental study

In designing a new database, you should not only examine business needs but also to examine the existing system. Most database projects are not built from scratch; Typically, institutions always exist to meet the specific needs of the existing system (which may not automatically calculated). Obviously, the existing system is not perfect, otherwise you will not have to set up a new system. But the old system of allows you to find some minor problems may be overlooked. In general, the study of absolute benefit to your existing system.

Standard definition of the object naming

Must define the database object naming. For the database table, from the start of the project is necessary to determine the table name is plural or singular form used. In addition, the alias table should be given to the definition of simple rules (for example, if the table name is a word, take the word alias on the first four letters; if the table name is two words, two words on the respective merits of the first two letters composed of four letters long alias; if the name of the table formed by the three words, you may wish to start from the two words in depicting a word from the last two letters out again, the result is composed of four letters long alias, the remaining and so on) to work with the table, the table name can be prefixed with the table WORK_ appended to the name of the application. Table column [field] to adopt a set of design rules for the key. For example, if the key is numeric type, you can use as a suffix _N; if a character type, you can use _C suffix. The column [field] name should use the standard prefix and suffix. Again, if your table has a lot of "money" field, you may wish to each column [field] Add a _M suffix. Also, the date column [field] is best to D_ starts with a name.

Checklist names, report names and naming conventions between the query name. You may soon be elements of these different database name confused. If you insist on uniform naming the different elements of these databases, at least in these objects, you should name beginning with Table, Query or Report a distinction between such a prefix.

If using Microsoft Access, you can qry, rpt, tbl, and mod symbols to identify objects (such as tbl_Employees). I am in and when dealing with SQL Server to index the table tbl is also used, but I used sp_company (now with sp_feft_) identifies the stored procedure, because sometimes if I find a better approach will often save several copies. I realized when SQL Server 2000 with udf_ (or similar marks) identifies the function I wrote.

Sharp tools make good his tools

Ideal database design using tools such as: SyBase company PowerDesign, she supported the PB, VB, Delphe and other languages, you can connect via ODBC market more than 30 popular databases, including dBase, FoxPro, VFP, SQL Server, etc., in the future there chance I will focus PowerDesign use.

Data Model Resource Book for

People who are looking for an example model can read "data model resource book", a book by the Len Silverston, WH Inmon and Kent Graziano write, is a worthy of the best data modeling books. The book includes chapters covering a variety of data fields, such as personnel, other agencies and work performance. You can also refer to the other: [1] Xuan Wang Shan Sa division with Database System (Second Edition) Higher Education Press, 1991, [2] [U.S.] Steven M. Bobrowski with Oracle 7 and Client / server computing technology from Introduction to Mastery Liu Yuan, M. Electronic Industry Press, 1996, [3] weeks Meta Information System Modeling Method (B) Electronics and Information 1999 3, 1999

Imagine the future, but can not forget the lessons of the past

I found that asking the user how to view changes in future demand is very useful. This can be done to achieve two purposes: First, you can clearly understand the application design should be more areas in which flexibility and how to avoid performance bottlenecks; Secondly, Do you know Shi Xian no definite needs change users will work with you as surprised .

To remember past experiences and lessons! We should also be adopted by developers to share their insights and experience to help each other. Even if the user that they have no more support, we should carry out their education in these areas, we have faced such a moment, "Had a nice to do ..."

In the logical design before physical practice

Before the first in-depth physical design to logical design. With the large number of CASE tools emerge, your design can achieve very high logic level, you can usually overall better understanding of all aspects of database design needs.

Know your business

One hundred percent sure of your system to meet their needs from the customer point of view Do not you before ER (entity relationship) model even if a data Biao Zhong Jia Ru (how, you do not model? Then please refer to skills 9). Know your enterprise business development at a later stage of saving a lot of time. Once you have defined the business requirements, you can make many of their own decision-making process.

Once you think you have defined business content, your best customers with a system of exchange. Terms with customers and explain to them what you think and what you hear. May also be used, such terms will be and must express the relationship between the base system. So you can let your customers correct understanding of your own design and then make the next step in the ER.

Create a data dictionary and ER diagrams

Must take the time to create ER diagrams and data dictionary. Which should at least contain the data type of each field in each table and the primary foreign key. Create ER diagrams and data dictionary is indeed a time-consuming, but for other developers to understand the overall design is absolutely necessary. The sooner the better they can help to avoid creating possible confusion in future face, so you can make any person a clear understanding of how the database to get data from the database.

Have a chart of the latest documents such as the ER can not be overstated its importance, which shows the relationship between tables is useful, the data dictionary will explain the purpose of each field and any possible aliases. Documented on the SQL expression for this is entirely necessary.

Create mode

A chart worth a thousand words: the developer not only to read and realize it, but also use it to help themselves and user dialogue. Model helps to improve collaboration efficiency, so that the database design in advance is almost impossible to major problems. Model need not get very complicated; even the simple to the handwriting on a piece of paper on it. Only to ensure the logical relations in the future can produce benefits.

From the start with input and output

The definition of database tables and fields in the demand (input), the first inspection of existing, or should have designed Chu's report, query and view (output) Yi decided Weile Zhichizhexie output which is necessary in the tables and fields. A simple example: If customers need a report in accordance with the zip code sorting, sections and sum, you must ensure that the zip code which includes a separate field and not to turn into the address field in zip code intermixed.

Report Tips

To understand how the user is often reports data: Batch or online filing? Time interval is daily, weekly, monthly, quarterly or annually? If necessary, create a summary table can also be considered. System-generated primary key in the report are difficult to manage. Users with system-generated primary key of the table with the Vice-key return often to search a number of duplicate data. Comparison of retrieval performance such as low and easily lead to confusion.

Understanding of customer needs

It seemed that this should be obvious, but the demand is from customers (internal and external customers here from the point of view). Do not rely on the user needs to write down the real needs of the customer's head. You want customers to explain their needs, and with the continued development, but also often ask clients to ensure their needs are still in the development of the purpose of being. A constant truth: "I saw I only know what I want" will inevitably lead to a lot of rework, because the database does not meet customer needs has never been written down standards. Worse still is that you need to explain to them only belongs to you, and may be completely wrong.

Part 2 - Design tables and fields

Check the changes

I will consider when designing a database to which data fields in the future may change. For example, surname is the case (note that the West's last name, such as women from the husband's surname after marriage, etc.). Therefore, storing customer information in establishing the system, I tend to separate names stored in a data table field, but also additional fields such as date of commencement and termination date, so that you can track the data entry changes.

Use meaningful field names

Once I had a project to participate in the development, which inherit from other programmers program, the programmers like to use the screen name the field in terms of data instructions, it was not that bad, but unfortunately, she also like to use some strange nomenclature, which was named by the Hungarian name and serial number of the combination control, such as cbo1, txt2, txt2_b so.

Unless you are using only the short field names for your system, otherwise the field as much as possible to describe more clearly. Of course, they should not done first, and such Customer_Shipping_Address_Street_Line_1, although very rich illustrative, but no one wants to type such a long name, the specific scales in your mastery.

Named using the prefix

If more than one table there is a lot of 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 identify the field.

Prescription data

Timeliness of data should include "Last Update Date / Time" field. Time tag to find data on the cause of the problem, according to the date of re-processing / reload data and clear the old data were particularly useful.

Standardization and data-driven

Standardization of data is not only convenient but also facilitates their others. For example, if your user interface to access external data sources (files, XML documents, other databases, etc.), you may wish to put the corresponding connection and path information stored in the user interface support table. Also, if the user interface like the implementation of the workflow tasks (email, printing stationery, records state changes, etc.), then the workflow generated data can be stored in a database. The total effort required pre-arranged, but if these processes using data-driven rather than hard-coding the way, then the strategy changes and maintenance will be easier. In fact, if the process is data driven, you can put a large responsibility to the user by the user to maintain their own workflow process.

Standard can not be too far

On those who are not familiar with the term Biaozhun Hua (normalization) The people, the standard will ensure the table's fields are the most Jichu of Yao Su, Er Zheyicuoshi help eliminate 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. In short, 3NF provides:

* Each value within the table can only be expressed once.

* Each line in the table should be a unique identifier (a unique key).

* The table should not be stored depends on the other key non-key information.

Compliance with 3NF standard database has the following characteristics: a group of dedicated storage table to link through the key associated data. For example, a store customer and the orders of the 3NF database may have two tables: Customer and Order. Order table does not contain any information related customers in order, but the table is kept a key, the key point to Customer table contains the line of the customer information.

Higher level of standardization, but in a more standards are definitely better? The answer is not. In fact, some of the projects, and even the introduction of even 3NF database can give too much complexity.

For efficiency's sake, the table is not standardized and sometimes also necessary, so many examples. Once a development of live food analysis software is to use non-standard table to check the time from an average of 40 seconds down to two seconds or so. Although I have to do this, but I can not put a non-standardized data sheet as of course design. However, the specific operation is derived. So if the problem re-generate Expressed non-standard table is entirely possible.

Microsoft Visual FoxPro reporting skills

If you are using Microsoft Visual FoxPro, you can use a user-friendly field names instead of numbers in the name: for example with the Customer Name instead of txtCNaM. In this way, when you use the wizard [Wizards, the people of Taiwan as 'Wizard'] to create forms and reports, its name will make people who are not programmers easier to read.

Not active or not used indicator

Add a field where the records that are no longer an active winter ridge in the business use. Whether customers, employees or others, this can help to re-run the query when the filter active or inactive. It also eliminates the use of new user data in some of the problems faced by, for example, some records may not be used for them, and then delete when you can play a preventive role.

Use the definition of the role of entities belonging to a category column [field]

The need to belong to a particular category or a specific definition of the role of things to do, you can use the role of the entity to create a specific time correlation, which can be self-documenting.

The implication is not so PERSON entity with a Title field, but rather, why not PERSON_TYPE PERSON entities and entities to describe the officer? For example, when John Smith, Engineer upgraded to John Smith, Director and finally climbed to John Smith, CIO of the high, and all you do is change the two tables of the relationship between PERSON and PERSON_TYPE key, while an additional Date / Time field to know when changes occur. In this way, your PERSON_TYPE PERSON table to include all possible types, such as Associate, Engineer, Director, CIO or CEO, etc..

There were alternative is to change PERSON records to reflect changes in the new title, but this way can not track individuals over time the location of a specific time.

Named agencies with common data entities

The easiest way to organize data is to use common names, such as: PERSON, ORGANIZATION, ADDRESS and PHONE so. When you put the general name used together or create the appropriate Vice-specific entity, you get their special version used. The beginning of the main reason for using a general term for all of the specific user can be on abstract things concrete.

With these abstractions that you can identify in the first two used their own special name, for example, PERSON may be the Employee, Spouse, Patient, Client, Customer, Vendor, or Teacher, etc.. Similarly, ORGANIZATION may be MyCompany, MyDepartment, Competitor, Hospital, Warehouse, Government and so on. Finally ADDRESS can be specified for the Site, Location, Home, Work, Client, Vendor, Corporate, and FieldOffice so.

General term used to identify the abstract "things" category allows you to in the associated data to meet business requirements were enormous flexibility, while It could also significantly reduce the amount of data storage required for redundancy.

Users from around the world

Used in the design of the international nature of the network or with other databases, we must remember that most countries have different field formats, such as zip code, etc. Some countries, such as New Zealand, said no one postcode.

Duplication of data need to use separate data sheet

If you find yourself repeating the input data, create new tables and new relationships.

Each table should be useful to add the three fields

* DRecordCreationDate, under the default in VB is Now (), but under the default in SQL Server GETDATE ()

* SRecordCreator, in SQL Server under the default is NOT NULL DEFAULT USER

* NRecordVersion, recorded version of the mark; contribute to an accurate description of the record null data or data loss occurs because

On the address and telephone number of fields used

Short description of the street address on the row is not enough. Address_Line1, Address_Line2 and Address_Line3 can provide greater flexibility. Also, telephone numbers and e-mail address is best to have their own table, during which the type and marked with their own category.

Should be careful with over-standardization, this may lead to performance problems. Although the address and phone list can usually achieve the best separation, but if you need frequent access to such information, may be stored in the parent table "preferred" information (such as Customer, etc) is more appropriate more. Non-standardized and accelerated access to a certain compromise between the meaning.

Use more than one name field

I am surprised that many people stay in the database, give name of a field. I think only just started the developers will do, but in fact this is very common online. I suggest that first and last name should be handled as two fields, and then check the time then they are combined.

I am most commonly used is the same table to create a computed column [field], by Lian Jie Biao Zhunhua can automatically after the fields that Shuojubiandong the time it Yegenzhaobian. However, doing so in a very clever use of modeling software Caixing. In short, the method used to connect field can effectively isolate the user interface of applications and developers.

Beware of the case mix of the object names and special characters

Over the past one of the things I was most annoyed with the case mix is the database object names, such as CustomerData. This problem to Oracle database from Access there. I do not like using this method of case-mix of the object name, the results also have to manually modify the name. Think about this database / application can be mingled with more powerful databases that day? Using all caps and includes breaks under the program name has a better readability (CUSTOMER_DATA), the object name Never leave a space between the characters.

Careful to retain the word

To ensure that your field names do not and retention of words, the database system or common access method Chongtu instance, been recent I wrote in a li ODBC Connection Cheng Xu has a table, which Jiuyong Le DESC Zuoweishuoming field names. Consequences can be imagined! DESC is a reserved word DESCENDING abbreviation. Table of a SELECT * statement Daoshi can use, but I get is a lot of useless information.

To maintain the consistency of field names and types

In the Name field and specify the data type for the time necessary to ensure consistency. If the field in a table called "agreement_number", you do not change her name in another table "ref1". If the data type is an integer at a table, another table that may become a character of another. Remember, you finish your own living, and other people have to do with your database.

Carefully select the type number

In SQL, use smallint and tinyint type to be especially careful, for example, if you want to see the total monthly sales, the total amount of your field type is smallint, then the total amount of more than $ 32,767 if you can not be calculated to operate.

Remove tag

In the table contains a "Delete Marked" field, so lines can be marked for deletion. Not alone in the relational database to delete a line; the best use of clear procedures and data integrity to be careful to maintain the index.

Avoid triggers

Trigger function can often be achieved by other means. When the debugger triggers may be interference. If you really need to use triggers, you'd better focus on its documented.

Release mechanism contains

Suggest that you introduce in the database, version control mechanism to determine the version of the database in use. In any case you have to realize this requirement. Over time, the user's needs will always change. Ultimately may be asked to modify the database structure. Although you can check the new field or the index to determine the version of the database structure, but I find the version information is not directly stored in the database is more convenient for you? .

Leave enough margin to text field

ID type of text field, such as customer ID or order number, etc. should be set greater than generally imagined, because you probably will not be long because you want to add extra characters and endless embarrassment. For example, suppose your client ID is 10 digits long. Then you should set the length of the database table fields 12 or 13 characters long. This is considered a waste of space? A little, but not as much as you think: a field extended three characters in a record 1 million, plus a bit of the index case was only to occupy the entire database of more than 3MB of space. But they do not need the additional space occupied by the future reconstruction of the entire database can scale up to achieve the database. ID card number from 15 into 18 is the best and most tragic example.

Column [field] Naming Tips

We found that if you give each table column [field] were all unified prefix, then the time in preparing the SQL expression will be greatly simplified. This also does have shortcomings, such as destruction of the automatic table connection tool, which the public out in [the field] were linked with some of the database, but sometimes these tools do not even have to connect error Well. Here is a simple example, suppose there are two tables:

Customer and Order. Customer table prefix is cu_, so the table field names are as follows: cu_name_id, cu_surname, cu_initials and cu_address so. Order table prefix is or_, so the field names are:

or_order_id, or_cust_name_id, or_quantity and or_description so.

This selected all the data from the database of SQL statements 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

In the absence of these prefixes are written in this case looks like (using an alias to distinguish):

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 tables and even more out [field] you know this technique more useful.

Part 3 - Selection keys and indexes

Data mining to pre-planned

I was once where a client departments to deal with more than 80,000 copies of contact, while completing the necessary data for each client (this is not a small live). I also identified from a group of customers as the target market. When I started to design tables and from the field, I try to not increase too much in the main index fields to speed up the database speed. Then I realized that a specific group of queries and information extraction is neither accurate nor fast speed. The results had to rebuild the main index and merge the data field. I found a direct scheme is the key - when I want to create the system type number to find the time why we should do as the main index fields? I can use to search fax number, but it is almost like the same type of system is not important to me. The latter as the main field, the database updated to re-indexing and retrieval much faster.

Operational data warehouse (ODS) and data warehousing (DW) data in both environments there is a difference index. In the DW environment, you need to consider is how to organize the sales department sales activities. They are not database administrators, but they determine the form of key information. Here the designer or database staff database structure should be analyzed to determine the performance and the best conditions between the correct output.

Using the system generated primary key

These same skills one, but I find it necessary to repeat here to remind you. If you always use in the design of the database when the system-generated key as the primary key, you actually control the integrity of the database index. Thus, the database and non-interrupting mechanism to control the storage of data in each row access.

Generated by the system as the primary key and a key advantage: When you have the same key structure, is easy to find flaws in logic.

Decomposition field is used to index

In order to isolate and contain the field name fields to support user-defined reports, consider the decomposition of other Ziduan (or primary key) its constituent Yao Su In order to of users can be indexed. The index will speed up SQL and Report Builder script execution speed. For example, I usually have to use the case of SQL LIKE expression to create reports, as case number field can not be broken down into year, serial number, case type and the defendant code and other factors. Performance will deteriorate. If the year and type of field can be decomposed into an index field, then these statements will be up and running much faster.

Key Design Principle 4

* To create a foreign key related fields.

* All keys must be unique.

* Avoid using composite keys.

* Foreign key is always associated with a unique key field.

Do not forget to index

Index is to obtain data from the database one of the most efficient manner. 95% of the database performance problems can be solved using indexing technology. As a rule, I usually use the logical primary key unique group index, keys on the system (as stored procedures) using only non-group indexes, on any foreign key column [field] with non-group index. However, the index is like salt, too much food on the salty. You have to consider how much space a database, how to access the table, there are these visits are mainly used for reading and writing.

Most database Du index automatically Chuangjian the primary key field, but can not forget the index foreign keys, which are also frequently used keys, such as running queries Xianshi the main table and all associated tables documenting an article of the lingua franca. Also, do not index memo / note field, not an index of large fields (there are many characters), to do so will make the index take up too much storage space.

Do not index small tables used

Do not set any key to small data table, if they often do not even insert and delete operations did it. Insert and delete operations on the index maintenance may consume more than scanning the table space of time.

Do not put Social Security number (SSN) or identity card number (ID) selected as key

Never use the SSN or ID as a database key. In addition to other privacy reasons, the Government notes a growing tendency not to allow the SSN or ID for inter-related purposes other than income, SSN or ID required manual input. Never use the manual input of the key as the primary key, because once you enter the wrong, the only thing you can do is delete the entire record and then start from scratch.

I crack other people's programs, I saw many people SSN or ID has been used as a serial number, of course, even though doing so is illegal. And people also know that it is illegal, but they're used. Later, with the increase in identity theft crimes, my peers are now suffering from a huge framework of the data the SSN or ID to delete.

Do not use the user's key

In determining what the field with the key as the table when the users can be careful to edit the field. Usually do not choose where the user can edit the field as the key. This will force you to take the following two measures:

* After creating record the behavior of the user edit the field restrictions. If you do, you may find that your application needs a sudden change in the business, but users need to edit the field who can not edit the lack of sufficient flexibility. When users enter data until after the record-keeping system, a problem only to find them how to think? Remove reconstruction? If the record is not renewal, allow users to walk away?

* Detect and correct some key conflict. Typically, the fee also buttoned point energy, but to do so from a performance point of view the cost of relatively larger. Also, the correct key may force you break your data and business / user interface layer isolation.

They still revert to the old saying: Do you design to adapt to the user, rather than allow the user to adapt to your design.

Not to have to update the primary key of the reason is that the relationship mode, the main key to achieve the connection between the different tables. For example, Customer table has a primary key CustomerID, and customer orders are stored in another table. Order table or master key may be OrderNo OrderNo, CustomerID and date combinations. Whether you choose which key set, you need to store in the Order table, CustomerID to ensure you can place an order for users to find records of its orders.

Customer table if you modify the CustomerID, then you must find all related Order records in the table be modified. Otherwise, some orders will not belong to any client - even if the integrity of the database finished.

If the index level of integrity rules applied to the table, then do not write code and add a large number of deleted records of a case of a record is almost impossible to change the key and all the associated database records. And this process is often error plagued so should be avoided.

Optional key (candidate key) key sometimes call the shots

Remember, query data, not machines but people.

If you have an optional key, you may be key to further use it call the shots. In that case, you will have the ability to build strong index. This can prevent the use of a database of people have to connect to the database to properly filter the data. Strict control of the domain table in the database, this load is more striking. If the optional key really useful, and that is the main key to reach the standard.

My view is, if you have an optional key, such as the national table of state_code, you do not change the existing unique key can not create follow-up of the key. You have to do nothing but create no value data. If you follow-up because of excessive use of table keys [alias] to establish the relevance of this table, the operation load really need to consider the.

Do not forget the foreign key

Most databases index the primary key field is automatically created. But do not forget to index the foreign key field, they are the main table you want to check the records and associated records will be used each time. Also, do not index memo / notes field and not to index a large text field (many characters), this index will let you take a lot of database space.

Part 4 - ensure data integrity

Constraint rather than the mandatory use of data integrity business rules

If you follow the business rules to handle the demand, then you should check the business class / user interface: If business rules change later, it can only be updated. If the demand comes from the need to maintain data integrity, then the database level, need to impose restrictions. If you do use the constraint data layer, you must ensure that there is a way to update the reasons can not be bound check user to understand the language used to notify the user interface. Unless you are a long field names, or field name itself is not enough.

Whenever possible, please use the database system for data integrity. This includes not only achieved through standardization of data integrity but also functional. When writing data triggers can be added to ensure the accuracy of the data. Do not rely on the business layer to ensure data integrity; it can not guarantee that between tables (foreign keys) the integrity of the integrity of the rules could not be imposed upon the other.

Distributed Data Systems

On the distributed system is concerned, whether you copy all the data in each site or the data stored in one place what should be estimated before the next 5 years or 10 years of data. When you send data to other sites, the best field in the database, set some tags. The destination site after the receipt of your data to update your tags. For this data, please write your own, or a batch scheduler to run a specific time interval and not let users transfer data after a day's work. To maintain local copies of your data, such as calculation of constant and interest rates, set the version number to ensure the data are exactly the same at each site.

Mandatory instructions integrity (referential integrity?)

There is no good way to be able to eliminate harmful data into the database after it, so you should have it removed before they entered into the database. Active database system integrity characteristics of the instructions. This can keep the data clean and can force developers to devote more time to deal with error conditions.


If there is many to one relationship between the two entities, but also may be turned to-many relationship, then you better start set-many relationship. Many to one relationship from the existing relationship into a-many-many relationship between the beginning than the much more difficult.

With the view

In order to your database and your application code to provide another layer of abstraction between, you can build your application specific view of the application without having to go directly access the data table. It could also mean changes in dealing with the database when you provide more freedom.

To maintain and restore the data plan

Consider the data to maintain strategy and included in the design process, pre-designed for your data recovery process. Use can be published to the user / developer to achieve the data dictionary to facilitate identification of data while ensuring the data of the source document. Write online update to "update check" for later re-treatment in case updated data loss.

Use the system stored procedure to do heavy work

Solve a lot of trouble to produce a database with high integrity solutions, I decided to package and Yi Xie The associated table feature set by providing a set of stored procedures to access Changgui Ge group in order to speed up and simplify the client code speed development. Database is not just a place to store data, it is also to simplify the coding.

Use the Find

The best way to control data integrity is to restrict user choice. As long as possible should be provided with a clear value to the user list to choose from. Type the code that will reduce errors and misunderstandings while providing data consistency. Particularly suitable to find some common data: country code, state code.

Part 5 - Various Tips

Document, document, document

For all shortcuts, naming standards and limits, and functions to be documented.

Used for table, column [field], triggers and other database tools add comments. Yes, this is a bit cumbersome, but in the long run, so do the development, support and very useful to track changes.

Depends on your use of the database system, there may be some software will give you some for your documents quickly get started. You may want to start talking, then get more details. Or you may want to periodically pre-arrangement, in

English into unicode solution

  • 01:40
  • View (0)
  • Comments (0)
  • Category: DataBase
  • Related Recommendation
  • Experience of database design (change) 2010-04-03

    A successful management system is: [50% + 50% of business software] is composed, while another 50% of the successful software [25% database + 25% of the programs] are composed of good and bad database design is a key. If you compared the life of the

  • Experience of database design - turn 2011-08-01

    A successful management system is: [50% + 50% of business software] is composed, while another 50% of the successful software [25% database + 25% of the program] is composed, good or bad database design is a key. If the enterprise's data compared the

  • Experience of database design 2011-04-25

    Reproduced to: http://www.williamlong.info/info/archives/87.html say oh well A successful management system is: [50% + 50% of business software] is composed, while another 50% of the successful software [25% database + 25% of the program] is composed

  • Experience of database design: Part 1 - Design the database before 2010-03-19

    If you compared the life of the enterprise data necessary for the blood, then the database design is the most important part of the application. Voluminous material related to database design, university degree courses where there are special about.

  • Experience of database design: Part 1 - Design a database before 2010-03-19

    If you compared the life of the enterprise data necessary for the blood, then the database design is the most important part of the application. Voluminous material related to database design, university degree courses where there are special about.

  • Experience in database design (1,2) 2010-11-18

    - From the network, the original author is unknown. . . - Zhuantie does not mean I totally agree with the view, hoping not to be misleading. . . A successful management system is: [50% + 50% of business software] is composed of While another 50% of t

  • Little experience in database design 2011-07-31

    Database design in a total project occupies a very large proportion, if designed well, unreasonable, in the development process behind the project do Gaidongkuaidui have a very big impact, it will spray a lot of time, what is summed up in the develop

  • Database design (change) 2010-03-26

    Speaking of databases, I think can not but talk about data structures. In 1996, I joined the university to learn computer programming, then the teacher told us: a computer program = data structure + algorithm. Although the current process of program

  • Experience in database design (3,4,5) 2010-11-18

    Part 3 - Selection keys and indexes To pre-planned data mining Where I was once a client departments to deal with more than 80,000 copies of contact, also complete the necessary data for each client (this is not a small live). I would also like to id

  • Experience of database design: Part 5 - Various Tips 2010-03-19

    1. Document, document, document all the shortcuts, naming conventions, limitations and functions should be documented. - Nickypendragon Using a table, columns, triggers and other database tools to add comments. Yes, this is a bit cumbersome, but in t

  • Experience of database design: Part 2 - Design tables and fields 2010-03-19

    1. Examine the various changes I will consider when designing a database to which data fields in the future may change. For example, last name is so (note that the West's last name, such as women from the husband's surname after marriage, etc.). Ther

  • Experience of database design: Part 4 - ensure data integrity 2010-03-19

    1. Use a constraint rather than business rule enforcement data integrity if you are under commercial rules to deal with demand, then you should check the business class / user interface: Ru Guo occurred after business rules change, it can only be upd

  • On database design experience (1,2) 2010-11-18

    - From the network, the original author is unknown. . . - Zhuantie does not mean I entirely share the view, hoping not to be misleading. . . A successful management system is: [50% + 50% of business software] formed, While another 50% of the successf

  • SQL database design experience 2009-06-19

    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

  • [Change] java database design techniques in the 14 2010-04-06

    The following 14 tips, many people in a large number of database analysis and design practice, gradually summed up. The use of these experiences, the reader can not help rigidly students, rote memorization, and to digest understanding, pragmatic, fle

  • [Turn] the prime database design experience 2011-02-20

    Popular understanding of the three paradigms, for a lot of good database design. In database design, in order to better application of three paradigms, we must understand the three popular paradigms (popular understanding is enough to understand, is

  • Database design experience in a small 2011-07-31

    Database design in a total project occupies a very large proportion, if designed well, unreasonable, in the process of making changes behind the development of the project will have a very big impact, will spray a lot of time, what is summed up in th

  • On database design experience (3,4,5) 2010-11-18

    Part 3 - Select the key and index Data mining to pre-plan I was once where a client departments to deal with more than 80,000 copies of contact information, and fill in the necessary data for each customer (this is not a small live). I also identifie

  • [Change] database design paradigm in layman's language 2010-07-08

    Original Address http://www.cnblogs.com/Bowen80/archive/2009/01/08/1371903.html When relational database design is to observe certain rules. In particular, database design paradigm is briefly 1NF (first normal form), 2NF (second paradigm), 3NF (third

  • Database design skills of 14 2009-04-20

    1. Original documents and the relationship between entities can be one-on-one, one-to-many, many-to-many relationship. Under normal circumstances, they are one-on-one relationship: the one and only original documents corresponding to the correspondin