Database design experience talk
Part 1 - Before designing the database
This part is listed in 12 basic skills, including naming regulations and clarifying business needs.
Wait.
Part 2 - Design Database Table
24 guide skills, covering the design of the field, and the common you should avoid
Question, etc.
Part 3 - Select button
How to choose the key? Here is 10 techniques that specialize in the system generated primary key.
Relief, while and how to index fields to achieve optimal performance.
Part 4 - Guarantee data integrity
Discuss how to keep the database clear and strong, how to reduce harmful data to minimal
degree.
Part 5 - Various tips
Do not include other techniques in the above 4 parts, five flowers, with them
I hope that your database development will be more easily.
§ Part 1 - Before designing the database
- --──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────tes
■ Examinate the existing environment
When designing a new database, you should not only study business needs carefully and
Take out existing systems. Most database projects are not built from beginning to; usually,
There will always be existing systems used to meet specific needs (may not implement automatic meter)
Calculate). Obviously, existing systems are not perfect, otherwise you don't have to build a new system.
But research on the old system allows you to find some subtle problems that may ignore.
In general, examining existing systems is absolutely beneficial to you.
■ Define standard object naming specifications
Be sure to define the naming specification of the database object. For database tables, from project one
Starting to determine the table name is a plural or singular form. Also given the alias for the table
Simple rules (for example, if the table name is a word, the alias will take the word before the word
Letter; if the table name is two words, each of the first two letters of two words are taken
4 letters long alias; if the name of the table consists of 3 words, you may wish to
Take one of the words and then remove the two letters from the last word, the result is still
Composition 4 letters long alias, the rest of the sequential push), the table name can be
Add the name of the application that uses the table after prefix Work_. Column in the table [
Field] To adopt a set of design rules for the keys. For example, if the key is a digital type, you
You can use _n as a suffix;
If it is a character type, you can use a _c suffix. The name of the column [field] should be marked
Quasi-prefix and suffix. For another example, if there is a lot of "Money" fields in your table, you don't
Give each column [field] to add a _m suffix. Also, the date column [field] is best
D_ as a name head.
Check the naming specification between the table name, the report name, and the query name. You may be soon
The names of these different database elements are confused. If you insist on unify these
Different components of the database, at least you should start at the beginning of these object names
The prefixes such as Table, Query or Report are different.
If you use Microsoft Access, you can use QRY, RPT, TBL, and
MOD and other symbols to identify objects (such as TBL_EMPLOYEES). I am in and SQL
Server has used TBL to independent, but I use sp_company
(Now use sp_feft_) to identify stored procedures, because if I have discovered it
A better handling method often saves several copies. I am implementing SQL Server
Use UDF_ (or similar tags) when 2000, identify the function I have written.
If you want to do something, you must first use the ideal database design tools, such as:
Sybase's PowerDesign, she supports PB, VB, DELP HE and other languages,
ODBC can connect to more than 30 databases, including DBASE, FOXPRO, V FP, SQL Server, etc., I will have a chance to introduce in the future.
Use of PowerDesign.
■ Get Data Mode Resource Manual
People who are seeking example mode can read the book "Data Model Resource Manual",
The book is written by Len Silverston, W. H. Inmon and Kent Graziano,
An optimal data modeling book worth having. The book covered by the book covers a variety of data collar
Domain, such as people, institutions, and work performance, etc. Other you can also refer to: [1] Saiss
煊王 数据 数据 数据 系统 概 (第二 第二) Higher Education Press 1991, [2] [United States]
Steven M.Bobrowsk i is from Oracle 7 and Customer / Server Computing Technology from Getting Started
Go to Jingjing Liu Jianyuan et al, 1996, [3] Zhou Zhongyuan information system modeling
Method (below) Electronic and Informatization 1999 No. 3, 1999 Imagine Future, but not forgetting
I have discovered that the user seeking how users think is very useful. such
Do two purposes: First, you can clearly understand which place in the application design
It should be more flexible and how to avoid performance bottlenecks; secondly, you know that there is no prior.
The user will be surprised as you when the required demand changes.
Be sure to remember the lessons of the past! Our developers should also share themselves
Experience and experience help each other.
Even if the user thinks they don't need any support, we should also enter them.
In this regard, we have all been facing this moment. "I have to do this.
How good. ".
■ Logic design before physical practice
The logic design is first taken before deeply in-depth physical design. With a lot of Case tools
Constantly emerging, your design can also reach a considerable logic level, you can usually
A better understanding of the aspects of the database design is better understood from the whole.
■ Understand your business
Don't be able to meet your needs before you meet your needs before you meet your needs.
ER (physical relationship) mode Add even one data sheet (why, you have not mode yet?
Then please see the skills 9). Understanding your business business can save in the future development phase
plenty of time. Once you have identified your business needs, you can make many decisions yourself.
Once you think you have clarified your business content, you should make a system with your customers.
Communication. Adopt customer terms and explain to them what you think and you are heard.
At the same time, it should also be used, and the system will be used to express the system's relational base. such
You can let your customers correct your own understanding and do the next ER design.
■ Create a data dictionary and ER chart
Be sure to take some time to create an ER chart and data dictionary. At least at least each
The data type of a field and the primary key in each table. Create an ER chart and data dictionary
It is completely necessary when there is a little fee but it is entirely designed to understand other developers. More
Early creation can help to avoid the possibility of being facing in the future, so you can let any understand
According to people in the library, they are clear how to get data from the database.
There is a top new document such as the ER chart, and how it emphasizes it, this
It is useful to indicate the relationship between the table, and the data dictionary illustrates the purpose of each field and
Any alias that may exist. This is exact essential for documentation for SQL expressions.
■ Create mode
A chart wins thousand words: developers must not only read and implement it, but also
Use it to help yourself and your user talk. Pattern helps improve collaborative performance, so
A large problem is almost impossible in database design.
The pattern doesn't have to be very complicated; it can even be simple to write on a piece of paper.
It is only necessary to ensure that the logical relationship on it can produce benefits in the future.
■ From the input and output
When defining database tables and field requirements (input), you should first check existing or
Reports, queries, and views (outputs) have been designed to determine which are the necessary tables and fields to support these outputs. For a simple example: If the customer needs a report according to
Postal coding sort, segmentation and summation, you have to ensure that separate postal codes
Section and do not put the postal coding into the address field.
■ Report Tips
To understand how users report data: batch or submit reports online?
The time interval is daily, weekly, monthly, every quarter or every year? If necessary,
You can consider creating a summary table. The primary key generated by the system is difficult to manage in the report. User
The table with the system generated primary key is used to retrieve a number of repetitive data.
Such retrieval performance is relatively low and it is easy to cause confusion.
■ Understand customer needs
It seems that this should be obvious, but the demand is from the customer (here you want to
The perspective of internal and external customers). Don't rely on the needs of users, real
Demand in the head of the customer. You have to let customers explain their needs, and the development continues,
It is also necessary to ask customers to ensure that their demand is still in the purpose of development. A constant true
The reason is: "Only I saw me know what I want" will inevitably lead to a lot of
Request, because the database does not meet the customer's demand standards that customers have never written. Worse
It is your explanation of your needs only belong yourself, and it may be completely wrong.
§ Part 2 - Design tables and fields
- --──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────tes
■ Check a variety of changes
I will take into account what data fields will change in the future when designing the database.
more. For example, the surname is this (note is the last name of Westerners, such as women get married.
From the surname of the husband, etc.). So, when building system storage customer information, I tend to be in separate
Store the name field in a data sheet, and also attach the starting day and termination day, this
You can track changes in this data entry.
■ Use meaningful field name
One time I participated in a project, which inherited from other programmers.
Program, the programmer likes to display the data indicating the use of data in the screen, this is not
Lai, but unfortunately, she also likes to use some strange naming method, and their naming adopts Hungarian
Combined forms of nomenclature and control serial number, such as CBO1, TXT2, TXT2_B, and the like.
Unless you use the system that is only to your abbreviated field name, please ask as much as possible
Field descriptions are clear. Of course, don't do your head, such as
Customer_shipping_address_street_line_1, although it is very illustrative,
But no one is willing to type such a long name, the specific scale is in your grasp.
■ Name the prefix
If you have a lot of fields in multiple tables (such as firstname), you don't
Use a particular table prefix (such as cuslastname) to help you identify the field.
Time data should include the "Recent Update Date / Time" field. Time tag
The reason for finding data issues, reprocessing / overloading data by date and clears the old data
use.
■ Standardization and data driver
The standardization of data is not only convenient and convenient for others. For example, fake
If your user interface is to access the external data source (file, XML document, other databases, etc.),
You may wish to store the corresponding connection and path information in the user interface support table. Also, if
Fruit user interface to perform tasks such as workflow (send mail, print letterhead, modified record
Status, etc.), then data that generates a workflow can also be stored in the database. Pre-arranged
There is always a need to pay, but if these processes use data drivers rather than hard coding,
Then there will be much more convenient changes and maintenance. In fact, if the process is data driven,
You can push a considerable responsibility to the user, by the user to maintain your workflow process. ■ Standardization can't be over
For those who are not familiar with standardization (Normalization), standardization
It can guarantee that fields in the table are the most basic elements, and this measure can help eliminate data
Data redundancy in the library. There are several forms of standardization, but Thi RD Normal Form
(3NF) is often considered to be the best in performance, scalability and data integrity.
Balance. Simply, 3NF regulations:
· Each value in the table can only be expressed once.
· Each line in the table should be unique (with unique keys).
• Non-bond information dependent on other keys should not be stored within the table.
Compliance with 3NF standards has the following characteristics: There is a set of tables to store through
The associated data connected to the key. For example, a place to store customers and their own order
3NF databases may have two tables: Customer and Order.
The ORDER table does not contain any information about the order related to the customer, but a key will be stored in the table.
Value, the key points to the line that contains the customer information in the Customer table.
Higher levels of standardization are also available, but it is better to be better? The answer is not
for sure. In fact, for some projects, even 3nf may give the database
Entering the complexity of too high.
For efficiency, it is also necessary to standardize the table, such an example.
There are a lot of people. There have been a development of catering analysis software is the use of non-standardized tables.
Dimed from an average of 40 seconds to about two seconds. Although I have to do this, I am never
Non-standardization of data sheets as a design concept. And the specific operation is nothing is
Derive. So if you anticipate non-standardized tables, it is entirely possible.
Microsoft Visual FoxPro Report Tips If you are using
Microsoft Visual FoxPro, you can use it to replace the user-friendly field name
Name of the number: For example, use Customer Name instead of TXTCNAM. This way, when you use
Guide [Wizards, Taiwanese, "Elf"] When you create a form and report, its name
Will make people who are not programmers easier to read.
■ Isoactive or not used indicator
Add a field indicating whether the record is no longer active in the business. Do not
Tube is a customer, the employee is still otherwise, so that it can help again run inquiry.
Watch is active or inactive. At the same time, it also eliminates the new user when using data.
Some problems, such as some records may no longer be used for them, then delete
It can play a certain precaution.
Use role entity definitions belonging to a category [field] to belong to a specific category
Or when something with a specific role is defined, you can create a specific time with a role entity.
Inter-related relationships can be achieved in self-documentation.
The meaning here is not to let the Person entity comes with a title field, but said to
What don't use a person entity and persons_type entity to describe people? for example,
When John Smith, Engineer is upgraded to John Smit H, Director and the most
After climbing to John Smith, CIO's high, and all you have to do is changing two
Table Person and Person_Type key value, increase a date / hour
There is a change in the field to know when the change occurs. This way, your Person_Type table package
With all Person's possible type, such as Associ ATE, Engineer,
Director, CIO or CEO, etc.
There is also an alternative to changing the Person record to reflect changes in the new title.
In this way, it is not possible to track the specific time of the position where you person is in time. ■ Nomenca parts with common entity
The easiest way to organize data is to adopt common names, such as: Person,
Organization, Address, and P Hone, etc. When you put these common names
When the word is combined or created a specific corresponding cop, you get the special
version. The main reason for adopting general terms at the beginning is that all specific users can
The abstract cause is particularly embodied.
With these abstractions, you can use your own specialty in the level 2 identity.
Name, for example, Person may be Employee, Spouse, Patient,
Client, Customer, Vendor or Teacher et al. same,
Organization may also be Mycompany, MyDepartment, Competitor,
Hospital, Warehouse, Government, etc. Finally, Address can be specifically
Site, Location, Home, Work, Client, Vendor, Corporate and
FieldOffice et al.
Using general abstract terms to identify "things" categories allow you to associate data
Great flexibility in meeting business requirements, while doing so can significantly reduce the number
According to the redundancy required for the storage.
■ Users from all over the world
Be sure to remember when designing a database or other international characteristics.
Most countries have different field formats, such as postal coding, etc., some countries, such as
New Zealand has no postal coding.
■ Data Repeat requires discrete data sheet
If you find yourself in repeating data, create a new table and a new relationship.
3 useful fields that should be added in each table *
DRECORDCREATIONDATE, default is now (), while in SQL Server
The next default is GetDate () * SRecordcreator, silently somewhat by SQL Server
NOT NULL DEFAULT USER * NRECORDVERSION, recorded version tag; help
Reasons for NULL data or loss data in accurate description of the records and calls
It is not enough to use multiple fields to describe the street address.
Address_Line1, Address_Line2 and Address_li NE3 can provide greater
Flexibility. Also, the phone number and email address are best to have its own data sheet,
Has its own type and tag category.
Over-standardization can be careful, doing so may result in problems in performance. although
The address and telephone table separation can usually achieve the best, but if you need to access this
Class information, perhaps storage "preferred" information (such as Customer et al, etc.) in its parent table
In order to be appropriate. Compromise between non-standardized and accelerated access is certain.
■ Using multiple names fields
I feel very surprised, many people leave a field in the database. I felt
I have only to do this, but I actually on the Internet.
all over. I suggest that the last name and the name are treated as two fields, and then in the query
I will combine them again.
I am most commonly used to create a computational column [field] in the same table, it can be self
Connect the standardized field, which is also changing when the data changes. but,
This is a very machine in the use of modeling software. In short, use the way to connect fields
Can effectively isolate the user application and developer interface.
■ Bewix object names and special characters mixed with cases
One of the most annoyed things in the past is the name of the object in the database,
For example, CustomerData. This problem exists from Access to the Oracle database.
I don't like to use this case-sensitive object naming method, and the result has to be manually modified. Think about it, this database / application can be mixed into the more powerful database.
That day? With all uppercases and contains the names of the underwriting, it has better readability.
(CUSTOMER_DATA), do not leave space between characters of the object name.
■ Be careful
To ensure that your field name is not or reserved, database system or common access method
Conflict, for example, there is a table in an ODBC connection program I have written recently, where it is used.
DESC as a description field name. The consequences can imagine! DESC is descending
Reserved words after writing. A SELECT * statement in the table is able to use, but I get it
It is a large pile of information free.
■ Keep a word name and type of consistency
Be sure to ensure consistency when named the field and specify the data type. if
The field is called "ag reement_number" in a table, you don't do it in another table.
Change the name to "REF1". If the data type is an integer in a table, then in another
There is a word shape in the table. Remember, you have done your own lives, others still use
Your database.
■ Carefully choose a digital type
Use the Smallint and Tinyint type in SQL, such as, for example,
If you want to look at the total monthly sales, your total field type is smallint, then,
If the total exceeds $ 32,767, you can't make a calculation.
■ Delete tag
In the table contains a "delete tag" field so that the row is marked as deletion.
Do not delete a row separately in the relational database; it is best to use clear data programs and
Carefully maintain the integrity of indexes.
■ Avoid using triggers
The functionality of the trigger can usually be implemented in other ways. Trigger when debugging the program
Can become interference. If you really need a trigger, you'd better focus on its documentation.
■ Contains version mechanism
It is recommended that you introduce version control mechanisms in the database to determine the version of the database.
this. This requires this requirement anyway. Time is long, the user's needs will always change
changing. It may eventually be required to modify the database structure. Although you can check new fields
Or claim to determine the version of the database structure, but I found that the version information is stored directly to
Isn't it more convenient in the database? .
■ Leave the balance of text fields
ID type text field, such as customer ID or order number, etc. should be set
More than the general imagination, because the time is not long, you will be more than half of you will add extra characters.
It is unpleasant. For example, suppose your customer ID is 10 digits. Then you should put the number
The length of the library table field is set to 12 or 13 characters long. Is this a waste of space? Yes
One point, but there are so many things that you think: a field plus 3 characters in 1 hundred
10,000 records, plus a little index, but the entire database is more
3MB space. But this additional space does not need to refactor the entire database in the future.
Realize the growth of the database size. The number of identity card has become 18 in 15 digits.
Good and the most tragic example.
■ Column [Field] Name Skills
We found that if you give a unified prefix, that
What will be greatly simplified when writing SQL expressions. This is really lacking
Point, such as destroying the role of the automatic table connection tool, the latter is the same as the public column [field]
Some databases are linked, but even these tools are sometimes not connected. Make a simple
Single example, suppose there are two tables:
Customer and ORDER. The prefix of the Customer table is CU_, so in the table
The child is as follows: Cu_name_id, Cu_Surname, Cu_Initials and
Cu_address, etc. The prefix of the ORDER table is OR_, so the child name is: or_order_id, or_cust_name_id, or_quantity,
OR_DESCRIPTION, etc.
This will be written as follows from the database from the database.
_______________________
Select * from custom000, Order
Where cu_surname = "myname"
And co_name_id = or_cust_name_id and iv_quantity = 1
_______________________
This looks like this in the case of these prefixes (distinguishes with alias):
_______________________
Select * from custom000, Order
Where customer.Surname = "myname"
And customer.name_id = Order.cust_name_id
And ORDER.QUANTITY = 1
_______________________
The first SQL statement is not type how much characters are typed. But if the query involves 5
Table and more columns [field] You know how much this skill is used.
§ Part 3 - Select key and index
- --──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────tes
■ Data mining should be pre-planned
I have a certain customer department to deal with more than 80,000 contact information and fill in
Write the necessary data for each customer (this is definitely not small). I have to identify a group.
Customer as a market goal. When I started from the beginning of the design table and field, I tried it is not
The main index increases too much field to speed up the running speed of the database. Then I realize
Specific group queries and information mining are neither accurate speed. The result is only in the main index.
Rebuild and incorporate data fields. I found there is an indication plan fairly key - when I
Why should I use a number as the main index field when I want to create a system type? I can
Search with fax numbers, but it's almost like system types.
want. Using the latter as the primary field, re-indexing and retrieval after the database is updated.
Available Data Warehouse (ODS) and Data Warehouse (DW) data in both environments
Indexes are different. In the DW environment, you have to consider how the sales department organizes sales.
active. They are not database administrators, but they determine the key information in the table. This
The designer or database staff should analyze the database structure to determine performance
And the best conditions between correct output.
■ Use the primary key generated by the system
This class is a skill 1, but I think it is necessary to repeat everyone here. If you always
Is the key to use the system generated as a primary key when designing the database, then you actually control
The index integrity of the database. In this way, the database and non-manual mechanisms are effectively controlled.
Access to each row in the storage data.
There is also a advantage that the system generated key as the primary key: when you have a consistent key structure
It is easy to find logical defects.
■ Decomposition field is used to index
In order to separate the named field and include the field to support user-defined reports, please consider
Solve other fields (or even primary keys)
For its constituent elements so that the user can index it. Index will speed up SQL and
Report generator script execution speed. For example, I usually have to use SQL.
Create a report in the case of a Like expression because the Case Number field cannot be decomposed into
Year, Serial Number, Case Type and Defendant Code and other elements. Sexuality
It will also become bad. If the annual and type fields can be broken down into index fields, then these reports
It will be much faster when running.
■ Key design 4 principle
1. Create an foreign key for the associated field.
2. All keys must be unique. 3. Avoid using a compound key.
4. Foreign key is always associated with unique key fields.
■ Don't forget the index
Indexes are one of the most efficient ways to get data from the database. 95% database
Can I use indexing techniques to be solved. As a rule, I usually logically
The primary key uses the unique group index to adopt a unique non-system key (as a stored procedure)
A group index, which uses a non-group index for any foreign key column [field]. However, the index is like
Salt, too much dish is salty. How big is your space for considering the database, how to interview
Ask, if there are such access to mainly used to read and write.
Most databases index automatically created primary key fields, but don't forget the index.
Key, they are also used frequently, such as running queries showing the primary table and all associated terms
Some records are used. Also, don't index the Memo / NO TE field, don't index big
Type field (there are many characters), so that the index will take much storage space.
■ Do not index common small tables
Do not set any keys for small data tables, if they often have insert and delete operations
Don't make this way. Index maintenance for these insertions and deletions may be empty than scanning
Consume more time.
Don't choose a social security number (SSN) or ID card number (ID) ordered
Do not use SSN or ID as a database of the database. In addition to the privacy reason,
The government is increasingly tending to use SSN or ID as other purposes other than income.
, SSN or ID requires manual input. Never use manual input keys as primary key,
Because once you enter an error, you can do it is to delete the entire record and start from the beginning.
When I was cracking the procedure of others, I saw that many people had been defended by SSN or ID.
Using a series number, of course, although it is illegal. And people also know this is illegal.
They have been used to it. Later, with the increase of stealing cases, I am now
The SSN or ID is deleted from a large stall data in the same line.
■ Do not use the user's key
When determining what field is used as a table, it is necessary to be careful that the user will
Edited field. In general, do not select the user-edit field as a key. such
Doing it forces you to take two measures:
1. Apply a restriction on the behavior of the user edit field after creating a record. If you do this
You may find that your application suddenly changes in business needs, and
The user needs to edit those flexible flexibility when they cannot be editable fields. When the user is
After entering the data until the record is found, I will find a problem. What do they think?
Delete rebuild? If the record cannot be rebuilt?
2. Propose some methods for detecting and correcting key conflicts. Usually, the expenses are all done,
But from performance to see this cost is relatively large. Also, the correction of the key can be
It will force you to break through your data and the isolation between the business / user interface.
So still retrospects an old saying: your design should adapt to the user rather than letting the user
Should be your design.
The reason why the primary key is updated is that the primary key is different in relational mode.
The association between the tables. For example, the Cu Stomer table has a primary key Customerid, and the guest
The order of the household is stored in another table. The primary key of the ORDER table may be Orderno or
ORDERNO, CUSTOMERID and date of date. No matter which key settings you choose,
You need to store Customerid in the Order Table to ensure that you can give the order
The user finds its order record.
If you modify Customerid in the Customer table, then you have to find out
All related records in the Order table modify it. Otherwise, some orders will not be
The integrity of the database is ended in any customer - the database is finished. If the index integrity rule is applied to the table level, then the large number of code is not written and attached.
In the case of deleting the record, it is almost impossible to change the key and database of a record.
Related records. And this process is often incorrect, so it should be avoided as much as possible.
■ Optional button (candidate button) can sometimes be a primary key
Remember, query data is not a machine but a person.
If you have an optional button, you may further use it for primary key. That kind, you will
It has the ability to build powerful indexes. This can prevent people from using the database
Connect the database to properly filter data. This negative on the database of strict control domain tables
The container is more awake. If the option is really useful, it is the level of the primary key.
My opinion is that if you have optional keys, such as State_code in the country,
Don't create subsequent keys on an existing unique key. It's nothing more than you have to do.
Built a worthless data. Establish this table as you have built this table because of the subsequent keys of excessive use table [alias]
The association, the operation load really needs to be considered.
■ Don't forget the foreign key
Most database indexes automatically created primary key fields. But don't forget the index outer key field,
They are used each time when you want to query records in the home table and their associated records. and also,
Don't index the Memo / Notes field and don't index large text fields (many characters),
This will make your index occupy a lot of database space.
§ Part 4 - Guarantee data integrity
- --──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────tes
■ Force data integrity with constraint rather than business rules
If you handle the needs in accordance with the business rules, then you should check the business level / use
Outside: If there is a change in business rules, it is only necessary to update. false
If the demand stems from the need for maintenance data integrity, it is necessary to apply on the database level.
Conditions. If you really use a constraint in the data layer, you have to ensure that there is a way to update the update.
The language can be used to notify the user interface by means of a constraint check. Unless you
The field name is very lengthy, otherwise the field name is not enough.
As long as it is possible, use the database system to implement the integrity of the data. This not only includes
Through standardized integrity, it also includes functionality of data. When writing data
You can also increase the trigger to ensure the correctness of the data. Do not rely on business strata to ensure data
Integrity; it does not guarantee the integrity of the table (foreign bond), so you can't add other finish
Above the whole rules.
■ Distributed data system
For distributed systems, you decide whether all data is replicated in each site or
Save data before a place should be estimated to be estimated next for the next 5 or 10 years.
According to the quantity. When you transfer the data to other sites, it is best to set it in the database field.
Some tags. New your tags after you receive your data at the destination site. For this
Data transmission, write down your own batch or scheduler to be interposed at a specific time
Do not let users transfer data after every day. Local copy your maintenance data,
For example, calculation constants and interest rates, setting the version number to ensure that the data is completely one every site.
.
■ Forced indication integrity (reference integrity?)
There is no good way to eliminate it after harmful data into the database, so you should
It will be removed before it enters the database. Activate the integrity features of the database system. This
Motion can keep data clean and can force developers to put more time to handle errors
Part.
■ Relationship
If there is a multi-pair relationship between the two entities, it is also possible to translate into many more
Relationship, then you'd better set up to multiple relationships at the beginning. From existing multi-pair relationship
The transformation is much more than a lot of relationships than a lot of relationships.
■ Using views
In order to provide another layer of abstraction between your database and your application code, you
You can establish a special view for your application without having to use direct access to the application.
According to the table. This is also equal to providing more freedom when processing database changes.
■ Presentation and resume development plan
Consider data-owned strategy and include your data recovery during the design process
process. It is possible to ensure that data identification of data dictionary to the user / developer can also ensure documentation on data source. Write online update to "update query" for later
A data loss can be re-process the update.
■ Use the storage process to make the system live
Solved a lot of trouble to generate a highly integrity database solution
After that, I decided to package some functional groups of associated tables and provide a set of regular stored procedures.
Visit each group to speed up the speed and simplify the development of customer code. Database is not just one
Where the data is stored, it also simplifies the encoded place.
■ Using Find
The best way to control data integrity is to limit the selection of users. As long as possible
Should be provided to the user a clear value list for its choice. This will reduce the typing code
Errors and misunderstandings simultaneously provide data consistency. Some public data is especially suitable for finding:
Country code, status code, etc.
§ Part 5 - Various tips
- --──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────tes
■ Document, documentation, document
For all shortcuts, naming specifications, restrictions, and functions must be programmed.
Use database tools that are added to the table, column [field], triggers. Yes, this
A little time, but in the long run, doing this for development, support, and tracking modifications.
Depending on the database system you use, some software may give you some for you.
Come up the documentation. You may wish to start now, then get more and more details.
Or you may want a periodic prevention, in the input of new data, along with your progress
Part of details. No matter which way you choose, you will always have your database documentation, or
Create a document inside the database itself or separately. This way, when you have been more than a year
After the room is back, I will make the second version, and the opportunity you make mistakes will greatly decrease.
Use common English (or any other language) instead of using the code why us
Coding frequently (such as 9935A may be the supply code of 'Tsingtao Beer',
4xF788-q may be an account code? Reason a lot. But users usually use English into
Thinking instead of the code. Accounting for 5 years may know what 4xf788-q is
West, but new can not be. Best to create a drop-down menu, a list, and a report
Sort by English name. If you need to be encoded, then you can attach the user from the code.
English language.
■ Save common information
It is useful to make a table to store general database information. I am often in this table.
Play the current version of the database, recently check / fix (FoxPro), related design documentation
Name, customer and other information. This can realize a simple mechanism tracking database, when customers
Complaining that their database does not meet the requirements of hope and contact you when you contact you.
The household machine / server environment is particularly useful.
■ Test, test, repeated test
After establishing or revising the database, you must use the new data to test data by the user.
segment. Most importantly, let users test and guarantee the data you selected with users.
Type meets business requirements. Test needs to be completed before putting the new database into actual services.
■ Check design
The common technology for checking the database design during development is through the application they support
Preface prototype check the database. In other words, for the prototype application of each final expression data,
Ensure that you check the data model and see how to remove the data.
Microsoft Visual FoxPro Design Tips for Complex Microsoft
Visual FoxPro database application, you can put all the main tables in a number
According to the library container file, then add other database table files and loads with the original database
Official document. Use these files as needed to connect to the primary table in the primary file. Such as
According to input, data index, statistical analysis, report to management or government departments and
Various types of read-only queries, etc. This measures simplifies the allocation of users and group permissions, and it is conducive to
The packet and division of the application function (stored procedure), so that the program must modify
Waiting for management.
