cool hit counter Database Design Experience Talk_Intefrankly

Database Design Experience Talk

A successful management system, attributable:[50% operations + 50% software] constituent, but (not) 50% The successful software has also [25% of the database + 25% approved procedure] constituent, Good or bad database design is a key。 If you compare the data of a business to the blood necessary for life, Then the design of the database is the most important part of the application。 There is a wealth of material on database design, It's also covered in university degree programs。 merely, As we have repeatedly stressed, A good teacher is no match for the teachings of experience。 So I summarize the detours I've taken over the years and what I've learned, And I found some professionals on the Internet who are good at database design to give you some tips on how to design a database harmony experience。 A selection of which 60 The best tips, and has written these tips into this article, For indexing purposes its contents are divided into 5 sections:

Part 1 - Before Designing the Database

This section lists the 12 Basic Tips, Includes naming convention harmony Define business needs etc.。

Part 2 - Designing Database Tables

A total of 24 guiding tips covering such things as field design in tables and common problems that should be avoided.

Part 3 - Selection Keys

How do you choose the keys?? Here it is. 10 A tip specifically related to the correct usage of system-generated primary keys, why else? when and how to index fields for best performance etc.。

Part 4 - Ensuring Data Integrity

Discuss how to keep the database clear harmony sturdy, How to minimize harmful data。

Part 5 - Various Tips

Other tips not included in the above 4 sections are varied and with them hopefully your database development work will be a little easier.

(prefix indicating ordinal number, e.g. first, number two etc)1 part- Before designing the database

Examination of the existing environment

When designing a new database, you should not only carefully study the business requirements but also examine the existing systems. Most database projects are not built from scratch; typically, there will always be existing systems within the institution that are used to meet specific needs (and may not have automated calculations implemented). Obviously, the existing system isn't perfect, or you wouldn't have to build a new one. But researching older systems can lead you to subtle issues that you might otherwise overlook. In general, it is definitely to your advantage to examine the existing system.

Defining a standard object naming convention

Be sure to define the naming convention for database objects。 For database tables, From the outset of the project, determine whether the plural or singular form of the epithet is to be used。 Also define simple rules for table aliases( for example, say, If the table name is a word, The alias takes the first part of the word 4 individual letters; If the table name is two words, Just take the first two letters of each word to form 4 Letter-length aliases; If the name of the table is given by 3 Words that make up, You might as well take one from each of the first two words and then two more letters from the last word, The result is still composed 4 Alphabetic long aliases, The rest in turn) For working tables, Table names can be prefixed with WORK_ Followed by the name of the application that uses the table。 Columns in the table[ (numeric, data) field] To adopt a set of design rules for keys。 for example, If the key is a numeric type, You can use _N as a suffix; If it is a character type then you can use _C suffix。 arrange[ (numeric, data) field] Names should use the standard prefix harmony suffix。 another example, If you have a lot on your watch“money” (numeric, data) field, You may wish to give each column[ (numeric, data) field] Add a _M suffix。 in addition, date line[ (numeric, data) field] preferably with D_ As the name hits the head。

Check table name、 Report Name harmony Naming convention between query names。 You may quickly get confused by the names of these different database elements。 If you insist on naming the different components of these databases consistently, At the very least you should start these object names with Table、Query perhaps Report etc. Prefix to distinguish。

If the use of Microsoft Access, You can use qry、rpt、tbl harmony mod etc. Symbols to identify objects( for example tbl_Employees)。 I'm in harmony SQL Server I've used it in my dealings. tbl to index the table, But I used sp_company ( Now use sp_feft_) Identifying Stored Procedures, Because at times I tend to save several copies if I find a better way to handle it。 I'm making it happen. SQL Server 2000 in case of need udf_ ( Or a similar marker) Identifies the function I wrote。

To do a good job, an artisan needs the best tools (idiom). Good tools are prerequisite the successful execution of a job

Adoption of ideal database design tools, for example:SyBase corporate PowerDesign, She supports PB、VB、Delphe etc. language, pass (a bill or inspection) ODBC It is possible to connect to popular commercially available 30 Multiple databases, include dBase、FoxPro、VFP、SQL Server etc., I will highlight future opportunities PowerDesign usage。

Access to Data Model Resource Manual

Those who are looking for sample patterns can read《 Data Model Resource Manual》 edition of a book, The book was written by Len Silverston、W. H. Inmon harmony Kent Graziano prepare, One of the best data modeling books to own。 The book includes chapters covering a wide range of data areas, For example, personnel、 mechanism harmony Work effectiveness etc.。 Others you can also refer to:[1] Sage Xuan  By Shan Wang  Introduction to database systems( second edition) loud etc. Education Press 1991、[2][ beautiful] Steven M.Bobrowski write Oracle 7 With customers/ Server Computing Technology from Beginner to Master  Liu Jianyuan (1936-), PRC novelist etc. interpret  Electronic Industry Press,1996、[3] Zhou Zhongyuan (1931-2005), PRC computer scientist  Information Systems Modeling Methodology( downwards)  Electronics and Informatization 1999 first year3 period,1999

Think about the future, but don't forget the lessons of the past

I find it useful to ask users how they see future changes in demand。 This serves two purposes: firstly, You can clearly understand where the application design should be more flexible and how to avoid performance bottlenecks; secondly, You know that when a change in requirements occurs that was not determined in advance the user will harmony You're just as surprised.。

Be sure to remember the lessons of the past! We developers should also help each other by sharing our experiences and lessons learned. Even if users think they don't need support anymore, we should educate them on this, we've all faced that "I wish I'd done this..." moment before.

Logical design prior to physical practice

Logical design is done before diving into physical design. With the plethora of CASE tools that continue to emerge, your designs can also reach a fairly high level of logic, and you can usually get a better understanding of all aspects of database design needed as a whole.

Know your business

Don't add even one data table to your ER (entity relationship) schema until you are 100% sure that the system meets its needs from the customer's perspective (what, you don't have a schema yet?). (Then please refer to Tip 9). Understanding your business can save a lot of time in the later stages of development. Once you have clarified your business needs, you can make many decisions on your own.

Once you think you've clarified what your business is about, you'd better have a systematic conversation with your client. Adopt the customer's terminology and explain to them what you think and what you hear. The relational base of the system should also be expressed in terms such as may, will and must. This way you can let your client correct your own understanding and then do the next step of ER design.

Creating a data dictionary harmony ER chart

Be sure to take the time to create ER chart harmony Data dictionary。 At a minimum, it should contain the data type for each field harmony The primary foreign key within each table。 create ER chart harmony Data dictionaries are a bit time-consuming, but it is absolutely necessary for other developers to understand the entire design。 The earlier you create, the more you can help avoid the potential chaos you face in the future, This allows anyone who knows the database to know how to get data from it。

There is a copy such as ER chart etc. The importance of the latest documentation cannot be overemphasized, This pair indicates between tables relations Very useful, The data dictionary, on the other hand, explains the purpose of each field and any aliases that may exist。 Right SQL This is absolutely necessary for the documentation of expressions。

creation mode

A chart is better than a thousand words: Developers need not only read harmony Implement it, And use it to help yourself harmony The user talks。 Models contribute to collaborative effectiveness, This makes it almost impossible for major problems to occur in the upfront database design。 The pattern doesn't have to be complicated; It can even be as simple as handwriting on a piece of paper。 It's just a matter of making sure the logic on it relations Future benefits can be generated。

Start with the input and output

In defining the database tables harmony Field requirements( input) time, You should first examine existing or designed reports、 Inquire harmony view( output) to decide which tables are necessary to support these outputs harmony (numeric, data) field。 Give a simple example: Suppose the customer needs a report sorted by zip code、 sublevel harmony beg harmony, You want to make sure that a separate zip code field is included instead of putting the zip code into the address field。

Reporting Tips

To understand how users typically report data: batch or online submission of reports? Is the time interval daily, weekly, monthly, quarterly or yearly? Also consider creating summary tables if needed. System-generated primary keys are difficult to manage in reports. User searches with secondary keys within tables that have system-generated primary keys often return many duplicates. Such a search performance is relatively low and confusing.

Understand customer needs

It seems obvious, But the demand is from the customer( Here's from the inside harmony External customer perspective)。 Don't rely on the user's written-down needs, The real need is in the customer's head。 You need to let the client explain their needs, And as development continues, Also ask the client frequently to ensure that their needs are still being developed for the purpose。 The one constant truth is:“ I only know what I want when I see it” Inevitably, this will lead to a lot of rework, Because the database didn't meet the requirements criteria that the client never wrote down。 What's worse is that your explanation of their needs is your own, And it could be completely wrong。

(prefix indicating ordinal number, e.g. first, number two etc)2 part- Design the table harmony (numeric, data) field

Checking for changes

When I design a database, I consider which data fields are likely to change in the future。 for example, say, That's the case with last names( Note that it is a Western surname, For example, a woman gets married and takes her husband's last name etc.)。 So, When you set up a system to store customer information, I tend to store last name fields in a single data table, It also adds a start date harmony The termination date etc. (numeric, data) field, This allows you to track changes in this data entry。

Use meaningful field names

Once I was involved in the development of a project, There are programs that are inherited from other programmers, The programmer likes to name fields with data indication terms displayed on the screen, That's not bad, But unfortunately, She also likes to use some strange nomenclases, It is named after Hungary harmony The combined form of the control sequence number, for example cbo1、txt2、txt2_b etc. etc.。

Unless you are using a system that is only geared towards your abbreviated field names, please make the field descriptions as clear as possible. Of course, don't overdo it, like Customer_Shipping_Address_Street_Line_1, which is very descriptive but no one wants to type such a long name; the exact scale is in your hands.

Adopt prefix naming

If there are many fields of the same type in multiple tables (such as FirstName), you may wish to use a table-specific prefix (such as CusLastName) to help you identify the field.

Time-sensitive data

Time-sensitive data should include“ Last updated date/ times” (numeric, data) field。 Time Stamp on Finding the Cause of Data Problems、 Reprocessing by date/ Reload data harmony Clearing old data is especially useful。

standardization harmony Data-driven

Standardization of data is convenient not only for yourself but also for others。 for example, say, Suppose your user interface wants to access an external data source( file、XML documentation、 Other databases etc.), You may as well connect accordingly harmony Path information is stored in the user interface support table。 in addition, If the user interface performs tasks such as workflows( Send a message、 Print the letterhead、 Modify the record status etc.), The data that produces the workflow can also be stored in the database。 It always takes effort to pre-arrange, But if these processes are data-driven rather than hard-coded, Then the policy changes harmony Maintenance is much easier。 In fact, If the process is data-driven, You can then place a considerable amount of blame on the user, It is up to the user to maintain their own workflow processes。

Standardization cannot be overdone

For those unfamiliar with the term normalization, standardization ensures that the fields in a table are all the most basic elements, and this measure helps eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is often considered to have the best balance of performance, scalability, and data integrity. In brief, 3NF provides that.

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

* Each row within the table should be uniquely identified (with a unique key).

* Non-key information that depends on other keys should not be stored in the table.

compliance with 3NF The standard database has the following characteristics: There is a set of tables dedicated to associative data linked by keys。 for example, say, of a depositing customer and its related order 3NF The database could then have two tables:Customer harmony Order。Order The table does not contain any information about the customer associated with the order, However, a key value is stored in the table, The key points to Customer The row in the table that contains the customer information。

There are higher levels of standardization, but is more standard necessarily better? The answer is not necessarily. In fact, for some projects, even 3NF may introduce too much complexity into the database.

Not standardizing tables is sometimes necessary for the sake of efficiency, and there are many examples of this. A job developing restaurant analytics software once reduced query times from an average of 40 seconds to about two seconds using non-normalized tables. While I have to do this, I would never take the non-standardization of data tables as a matter of course as a design philosophy. And the specific operation is nothing more than a derivation. So it is entirely possible to recreate non-normalized tables if something goes wrong with them.

Microsoft Visual FoxPro Reporting Tips

If you are using it Microsoft Visual FoxPro, You can replace the numbered name with a user-friendly field name: For example, use Customer Name replace txtCNaM。 such, When you use a wizard program [Wizards, Taiwanese call it‘ genie’] Create a form harmony When reporting, Its name will make it easier for those who are not programmers to read。

Indicators of inactivity or non-adoption

It would be useful to add a field to indicate whether the record is no longer active in the business. Whether it's a customer, employee, or whatever, doing so can help filter active or inactive status when running queries again. It also eliminates some of the problems that new users face when adopting data, for example, certain records may no longer be available to them and can serve as a precaution when deleting them again.

Use the role entity to define columns [fields] belonging to a category

When things that belong to a specific category or have a specific role need to be defined, role entities can be used to create specific temporal association relationships that can be self-documenting.

The meaning here is not to let PERSON The entity has Title (numeric, data) field, It's about saying, Why not? PERSON entity harmony PERSON_TYPE Entities to describe people? for example, say, while John Smith, Engineer Promoted to John Smith, Director Eventually climbing to John Smith, CIO high point of, And all you have to do is change two tables PERSON harmony PERSON_TYPE among relations the key value of the, Also add a date/ Time field to know when the change occurred。 such, Yours PERSON_TYPE The table contains everything PERSON The possible type of, for example Associate、Engineer、Director、CIO perhaps CEO etc.。

An alternative would be to change the PERSON record to reflect the change in the new title, although this would not track the exact time in time where the individual is located.

Use of commonly used entities to name institutional data

The easiest way to organize your data is to use common names, for example:PERSON、ORGANIZATION、ADDRESS harmony PHONE etc. etc.。 When you combine these common generic names or create specific sub-entities, You get a special version of yourself。 The main reason for using general terminology at the beginning was that all specific users were able to materialize abstract things。

With these abstract representations, you can adopt your own special name for the Level 2 identity, for example, PERSON might be Employee, Spouse, Patient, Client, Customer, Vendor, or Teacher, etc. Similarly, ORGANIZATION may be MyCompany, MyDepartment, Competitor, Hospital, Warehouse, Government, etc. Finally ADDRESS can be specific to Site, Location, Home, Work, Client, Vendor, Corporate and FieldOffice etc.

Using general abstract terms to identify categories of "things" gives you great flexibility in correlating data to meet business requirements, while also significantly reducing the amount of redundancy required for data storage.

Users come from all over the world

When designing a database that uses a network or other international characteristics, It is important to remember that most countries have different field formats, Like a zip code etc., Some countries, New Zealand, for example, does not have a postcode to say。

Data duplication requires discrete data tables

If you find yourself typing data repeatedly, Please create a new table harmony new relations。

3 useful fields that should be added to every table

* dRecordCreationDate, which defaults to Now() under VB, and GETDATE() under SQL Server

* sRecordCreator, defaults to NOT NULL DEFAULT USER under SQL Server

* nRecordVersion, the version tag of the record; helps to specify exactly why there is null data or missing data in the record

Use multiple fields for address and phone

It is not enough to describe a street address in just one line of records。Address_Line1、Address_Line2 harmony Address_Line3 It provides more flexibility。 in addition, telephone number harmony It's a good idea to have your own data sheet for your email address, It has its own type harmony Marking category。

Be careful with excessive standardization, Doing so may cause performance problems。 Although the address harmony Phone meter separation usually works best, But if this type of information needs to be accessed frequently, Perhaps in its parent table“ first choice” Information( for example Customer etc.) It's more appropriate。 Non-standardized harmony The compromise between accelerated visits makes sense。

Using multiple name fields

I was surprised, Many people give it in the database name Leave a field。 I think only new developers do this, But in practice this practice is very common online。 I suggest that the last name should be harmony The name is treated as two fields, Then combine them in a query。

I most often create a calculated column in the same table[ (numeric, data) field], With it you can automatically link the normalized fields, So that when the data changes, it changes with it.。 merely, You have to be very resourceful when employing modeling software to do this。 in conclusion, The use of connection fields allows for effective isolation of user applications harmony Developer Interface。

Beware of case-mixed object names harmony special characters

One of the things that has annoyed me the most in the past is having mixed case object names in the database, like CustomerData. This problem exists from Access to Oracle databases. I don't like the idea of using this case-mixed object naming method and ending up having to change the name manually. Think about it, will this database/application ever muddle through to the day when a more powerful database is adopted? Use names that are all uppercase and contain underscores for better readability (CUSTOMER_DATA), and never leave spaces between characters in the object name.

Beware of reserved words

Be sure that your field names don't conflict with reserved words, database systems, or common access methods; for example, a recent ODBC connection I wrote had a table in it that used DESC as a description field name. The consequences can be imagined! DESC is a reserved word after the abbreviation DESCENDING. A SELECT * statement in the table works backwards, but all I get is a bunch of useless information.

Keep the field name harmony Consistency of type

Be sure to ensure consistency when naming fields and specifying data types for them. If the field is called "agreement_number" in one table, you don't change the name to "ref1" in another table. If the data type is an integer in one table, don't make it a character type in the other table. Remember, when you're done with your own work, other people have to use your database.

Choose the type of number carefully

(located) at SQL use in smallint harmony tinyint Be especially careful with types, for example, If you want to see the monthly gross sales, Your total field type is smallint, or so, If the total amount exceeds $32,767 You can't do the calculations。

Delete marker

Include a "delete mark" field in the table so that rows can be marked as deleted. Do not delete a single row in a relational database; it is best to use a cleanup procedure and to carefully maintain the integrity of the index.

Avoiding the use of triggers

The function of a trigger can often be implemented in other ways. Triggers can become distracting when debugging programs. If you do need to employ a trigger, you'd better focus on documenting it.

Includes versioning mechanism

It is recommended that you introduce a version control mechanism in your database to determine the version of the database in use. You have to make this happen no matter what. Over time, users' needs always change. It may eventually require changes to the database structure. While you can determine the version of the database structure by checking for new fields or indexes, I find it more convenient to store the version information directly into the database, don't you?

Leave enough room for text fields

ID The text field of the type, For example, customers ID Or order number etc. etc. should be set larger than you might think, Because it doesn't take long, you're most likely embarrassed to add extra characters。 for example, say, Suppose your customer ID for 10 The number of digits。 Then you should set the length of the database table field to 12 perhaps 13 Characters are long。 Is this a waste of space?? There is a little, But not as much as you think: A field is lengthened 3 characters in there 1 Millions of records, Add a little indexing to make the entire database more occupied 3MB space。 But this extra space does not require the entire database to be refactored in the future to achieve a growth in database size。 The number of the ID card is from 15 Bit becomes 18 Bits are the best harmony The most tragic example。

Column [field] naming tips

We have found that writing SQL expressions is greatly simplified if you give each table a uniform prefix for its column [field] names. This does have drawbacks, such as breaking the automatic table linking tools, which associate common column [field] names with certain databases, but even these tools sometimes connect incorrectly. As a simple example, suppose there are two tables.

Customer harmony Order。Customer The prefix of the table is cu_, So the field names within that table are as follows:cu_name_id、cu_surname、cu_initials harmonycu_address etc.。Order The prefix of the table is or_, So the field name is:

or_order_id、or_cust_name_id、or_quantity harmony or_description etc.。

The SQL statement to select all the data from the database can then 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 it is written like this (with aliases to distinguish them).

Select * From Customer, Order Where Customer.surname = "MYNAME" ;

and Customer.name_id = Order.cust_name_id and Order.quantity = 1

The first SQL statement is not missing many characters. But if the query involves 5 tables or even more columns [fields] you know how useful this trick is.

(prefix indicating ordinal number, e.g. first, number two etc)3 part- selection key harmony index

Data extraction should be planned in advance

One of my customer departments was once going to deal with it 8 More than 10,000 contacts, Fill in the necessary data for each customer at the same time( This is definitely not a small job)。 I also want to identify a group of customers as market objectives。 When I designed the table from the very beginning harmony field at the time, I tried not to add too many fields to the main index to speed up the database。 Then I realized a specific group of queries harmony Information extraction is neither accurate nor fast。 As a result, the data fields were rebuilt and merged in the main index。 I find it quite critical to have an instruction plan—— Why use numbers as the primary index field when I want to create a system type lookup?? I can retrieve it by fax number, But it's almost like a system type that doesn't matter to me。 Use the latter as the primary field, The database is re-indexed after it is updated harmony Retrieval is much faster。

There is a difference in indexing data in the two environments, the operational data warehouse (ODS) and the data warehouse (DW). In a DW environment, you'll want to consider how the sales department is organizing sales activities. They are not database administrators, but they determine the key information within the table. Here the designer or database staff should analyze the database structure to determine the best conditions between performance and correct output.

Use the system-generated primary key

This category is the same as Tip 1, but I feel the need to repeat the reminder here. If you always design your database to use system-generated keys as primary keys, then you actually control the index integrity of the database. In this way, the database and non-manual mechanisms effectively control access to each row in the stored data.

Using system-generated keys as primary keys has another advantage: finding logical flaws is easy when you have a consistent key structure.

Decomposing fields for indexing

In order to separate the named fields harmony Contains fields to support user-defined reports, Consider breaking down other fields( Even the primary key) it makes up features so that users can index them。 The index will speed up SQL harmony The speed at which the report builder script executes。 for example, say, I usually use it when I have to SQL LIKE Creating reports in the case of expressions, owing to case number The field cannot be broken down into year、serial number、case type harmony defendant code etc. key constituent。 Performance can go bad too。 If annual harmony Type fields can be broken down into index fields so these reports run much faster。

Key Design 4 Principles

* Create a foreign key for the associated field.

* All keys must be unique.

* Avoid using compound keys.

* A foreign key is always associated with a unique key field.

Don't forget the index.

Indexing is one of the most efficient ways to get data from a database. 95% of all database performance problems can be solved using indexing techniques. As a rule, I usually use unique paired indexes for logical primary keys, unique non-paired indexes for system keys (as stored procedures), and non-paired indexes for any foreign key columns [fields]. However, indexing is like salt, too much and the dish gets salty. You have to consider how much space is available in the database, how the tables are accessed, and whether those accesses are primarily used for reads and writes.

Most databases index the primary key fields that are automatically created, But it does Don't forget the index. foreign key, They are also frequently used keys, For example, run a query to display the main table harmony A record of all associated tables is useful。 in addition, Do not index memo/note (numeric, data) field, Do not index large fields( There are many characters), Doing so can cause the index to take up too much storage space。

Don't index small tables that are commonly used

Don't set any keys for small data tables, especially if they have frequent insert and delete operations. Index maintenance for these insert and delete operations can consume more time than scanning a tablespace.

Do not select the social security number (SSN) or identification number (ID) as the key

Never use SSN or ID as a database key. In addition to privacy reasons, note that there is a growing trend for governments to not allow SSNs or IDs to be used for purposes other than income-related, and that SSNs or IDs need to be entered manually. Never use a manually entered key as your primary key, because once you make a typo, the only thing you can do is delete the entire record and start from scratch.

When I was hacking other people's programs, I saw a lot of people put SSNs or IDs that had also been used as series numbers, although of course it was illegal to do so. And people are aware that it's illegal, but they're used to it. Then, as cases of identity theft crimes increased, my current counterpart was painfully removing SSNs or IDs from a huge mess of data.

Don't use the user's keys

When determining what fields to use as keys for the table, be sure to be careful about the fields that the user will be editing. The usual case is not to select a user-editable field as a key. Doing so will force you to take two steps.

* Impose restrictions on the user's ability to edit fields after the record is created. If you do, you may find that your application lacks sufficient flexibility when business needs suddenly change and users need to edit fields that are not editable. What are users supposed to think when they don't find out there's something wrong with the system until they save the record after entering the data? Delete and rebuild? If the record cannot be reconstructed does it make the user walk away?

* Make some tests harmony The way to correct key conflicts。 usually, It's going to take a little effort to get it done, But in terms of performance, the cost is greater。 in addition, Key correction may force you to break through your data harmony commerce/ Isolation between user interface layers。

So to revert to the old adage: your design should adapt to the user rather than letting the user adapt to your design.

The reason for not allowing the primary key to be updateable is in relations mode, The primary key enables associations between different tables。 for example,Customer The table has a primary key CustomerID, And customer orders are stored in a separate table。Order The primary key of a table may be OrderNo perhaps OrderNo、CustomerID harmony Combination of dates。 Whichever key setting you choose, You all need to be in Order The table holds CustomerID to ensure that you can find the order record for the user who placed the order。

If you modify the CustomerID in the Customer table, then you must find all the relevant records in the Order table to modify it. Otherwise, some orders would not belong to any customer - and the integrity of the database would be finished.

If the index integrity rule is imposed to the table level, So don't write a lot of code harmony It is almost impossible to change the key of a record if a record is attached harmony All associated records in the database。 And this process is often wrong so try to avoid it。

Optional keys (candidate keys) can sometimes be used as primary keys

Remember, it's not the machine that queries the data but the person.

If you have an optional key, you may further use it as a primary key. That way, you have the ability to build a strong index. This would prevent the person using the database from having to connect to it and thus filter the data properly. This load is more striking on databases with strictly controlled domain tables. If the optional key is truly useful, it is up to the level of the primary key.

My opinion is that if you have optional keys, like state_code in the state table, you don't create subsequent keys on existing unique keys that can't be changed. You're doing nothing more than creating worthless data. If you create this kind of table association because of excessive use of the table's subsequent key [alias], the operational load really needs to be considered.

Don't forget the foreign keys

The primary key field that is automatically created by most database indexes. But don't forget about indexed foreign key fields, they are used every time you want to look up a record in the main table and its associated records. Also, don't index memo/notes fields and don't index large text fields (many characters), as doing so will make your index take up a lot of database space.

(prefix indicating ordinal number, e.g. first, number two etc)4 part- Ensure the integrity of your data

Enforce data integrity with constraints, not business rules

If you follow the business rules for the requirements, then you should check the business hierarchy/user interface: if the business rules change later, then just make updates. If the requirement stems from the need to maintain data integrity, then constraints need to be imposed at the database level. If you do employ constraints at the data layer, you want to ensure that there is a way to inform the user interface in a language that the user understands about the reason why the update fails the constraint check. Unless your field naming is lengthy, the field name itself is not enough.

Whenever possible, please use a database system to achieve data integrity. This includes not only integrity through standardization but also functionality of the data. Triggers can also be added to ensure correct data when writing data. Don't rely on the commerce layer to guarantee data integrity; it can't guarantee integrity between tables (foreign keys) so it can't be imposed on top of other integrity rules.

distributed data system

For distributed systems, You should estimate the future before you decide whether to copy all your data at each site or save it in one place 5 years or 10 The amount of data for the year。 When you transfer data to other sites, It's a good idea to set some tags in the database field。 Update your tags after your data is received at the destination site。 In order to carry out this data transfer, Write down your own batch or scheduler that runs at specific intervals without allowing users to transfer data after work every day。 Copy your maintenance data locally, For example, calculate constants harmony interest rate etc., Setting the version number ensures that the data is exactly the same at each site。

Mandatory indication of completeness (referential completeness?)

There is no good way to eliminate harmful data after it enters the database, so you should eliminate it before it enters the database. Activates the indication integrity feature of the database system. This keeps the data clean while forcing the developer to invest more time in dealing with error conditions.


If there is a many-to-one relationship between two entities, and there is a possibility that it will transform into a many-to-many relationship, then you are better off setting up a many-to-many relationship to begin with. It is much harder to shift from an existing many-to-one relationship to a many-to-many relationship than it is to start out with a many-to-many relationship.

Adopt a view

In order to be in your database harmony Provides another layer of abstraction between your application code, You can create a dedicated view of your application without having to have the application access the data table directly。 Do it also etc. gives you more freedom in dealing with database changes。

Keep the data harmony Resumption of plan development

Consider data retention strategies and include them in the design process to pre-design your data recovery process. Easy data identification using a data dictionary that can be published to users/developers while ensuring documentation of data sources. Write online updates to "update queries" for later reprocessing of updates in case of data loss.

Make the system do heavy lifting with stored procedures

After solving many troubles to produce a database solution with high integrity, I decided to encapsulate some functional groups of linked tables, Provide a set of regular stored procedures to access groups for speeding up harmony Simplify the development of customer program code。 A database is more than just a place to store data, It is also the place to simplify coding。

Use Find

The best way to control data integrity is to limit user choice. Wherever possible a clear list of values should be provided to the user for them to choose from. This will reduce errors and misunderstandings in typing code while providing data consistency. Certain public data are particularly suitable for lookups: country codes, status codes, etc.

(prefix indicating ordinal number, e.g. first, number two etc)5 part- Various tips

Documentation, documentation, documentation

For all shortcuts、 Naming specification、 limit harmony Functions are documented。

Adopt to the table、 column[ (numeric, data) field]、 trigger etc. Annotated database tool。 That's right, It's a bit of a hassly thing, But in the long run, Do this for development、 backing harmony It is useful to track modifications。

Depending on the database system you use, there may be software that will give you some documentation for you to get started quickly. You may want to start out in talking first and then get more and more details. Or you may want to periodically preschedule, entering new data while detailing each section as you progress. Whichever way you choose, always document your database, either within the database itself or by creating separate documentation. That way, when you go back and do version 2 after a year or so, you will have far less chance of making a mistake.

Use common English (or any other language) rather than coding

Why do we often use codes (e.g. 9935A might be a supply code for 'Tsingtao Beer', 4XF788-Q might be a billing code)? There are many reasons. But users usually think in English rather than coding. Accountants who have been working for 5 years may know what the 4XF788-Q is, but newcomers may not. When creating drop-down menus, lists, and reports it is best to sort them by English name. If you need to code, then you can attach the English that the user knows next to the code.

Save frequently used information

It is useful to have a table dedicated to general database information. I often store in this table information about the current version of the database, recent checks/fixes (for FoxPro), names of associated design documents, clients, etc. This allows for a simple mechanism to track the database, which is particularly useful for non-client/server environments when customers contact you complaining that their database is not as good as they would like it to be.

Test, test, test again and again

After creating or revising the database, you must test the data fields with the new data entered by the user. Most importantly, let users test and work with them to ensure that the data type you choose meets business requirements. Testing needs to be done before putting the new database into actual service.

Check the design

A common technique for checking the database design during development is to check the database through the application prototype it supports. In other words, for each prototype application that ultimately expresses data, make sure you examine the data model and see how to take out the data.

Microsoft Visual FoxPro Design Tips

Against complex Microsoft Visual FoxPro For database applications, You can put all the master tables in one database container file, Then add the other database table files harmony Loading of special files related to the original database。 Use these files to link to the master table in the master file as needed。 For example, data entry、 Data Index、 Statistical analysis、 Provide reports and various read-only inquiries to management or government departments etc.。 This measure simplifies the user harmony The assignment of group permissions, And it's good for application functions( Stored procedures) The grouping of harmony divide, This makes it easy to manage when the program must be modified。

transfer from Jerry Zhou's column , original address.

1、Chain Block Technology Issue 04
2、The Most Promising New Virtual Currency Ethereum Seizes the Opportunity to Challenge the Market Highs as a New Era Dawns
3、Its tomorrow Get in the car TA will take you to experience a different kind of smart driving
4、Jedi Quest 2018 plans pushing new gun maps to improve live OB system
5、First Press Conference of World IoT Expo 2018 Held in Beijing

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送