Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how many parts of database design". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Part 1-before designing the database

This section lists 12 basic techniques, including naming conventions and identifying business requirements.

Part 2-designing database tables

There are a total of 24 guiding techniques, covering the design of fields in the table and common problems that should be avoided.

Part 3-Select key

How to choose the key? Here are 10 tips specifically related to the correct use of system-generated primary keys, when and how to index fields for best performance.

Part 4-ensuring data integrity

Discuss how to keep the database clear and robust and how to minimize harmful data.

Part 5-Tips

There are a variety of techniques not included in the above four sections, and I hope your database development work will be easier with them.

Part 1-before designing the database

Examine the existing environment

When designing a new database, you should carefully study not only the business requirements but also the existing systems. Most database projects are not built from scratch; often, there are existing systems within the organization to meet specific needs (automatic calculation may not be implemented). Obviously, the existing system is not perfect, otherwise you don't have to build a new system. But the study of the old system allows you to discover minor problems that may be overlooked. Generally speaking, it is definitely good for you to examine the existing system.

Define standard object naming conventions

Be sure to define a naming convention for database objects. For database tables, determine whether the table name is plural or singular from the beginning of the project. In addition, you need to define simple rules for the aliases of the table (for example, if the table name is a word, take the first four letters of the word; if the table name is two words, take the first two letters of each word to form a four-letter alias. If the name of the table consists of three words, you might as well take one from each of the first two words and then take two letters from the last word, resulting in a 4-letter alias, and so on.) for a worksheet, the table name can be prefixed with WORK_ followed by the name of the application that uses the table. The columns [fields] in the table adopt a set of design rules for keys. For example, if the key is a numeric type, you can use the _ N suffix; if it is a character type, you can use the _ C suffix. Standard prefixes and suffixes should be used for column [field] names. For example, if you have many "money" fields in your table, you might as well add a _ M suffix to each column [field]. Also, the date column [field] is best preceded by the name D_.

Check the naming conventions between table names, report names, and query names. You may soon be confused by the names of these different database elements. If you insist on naming the different parts of these databases uniformly, you should at least distinguish them with prefixes such as Table, Query, or Report at the beginning of these object names.

If you use Microsoft Access, you can use symbols such as qry, rpt, tbl, and mod to identify objects (such as tbl_Employees). I have also used tbl to index tables when dealing with SQL Server, but I use sp_company (now sp_feft_) to identify stored procedures, because sometimes I tend to save several copies if I find a better way to handle it. When I implement SQL Server 2000, I use udf_ (or similar tags) to identify the functions I write.

If you want to do good work, you must sharpen its tools first.

Use ideal database design tools, such as SyBase's PowerDesign, which supports PB, VB, Delphe and other languages. Through ODBC, you can connect more than 30 popular databases in the market, including dBase, FoxPro, VFP, SQL Server and so on. I will focus on the use of PowerDesign in the future.

Get the data schema resource manual

Those looking for sample patterns can read the data Schema Resource Manual, written by Len Silverston, W. H. Inmon, and Kent Graziano, which is the best data modeling book worth having. The book includes chapters covering a variety of data areas, such as people, institutions, and productivity. You can also refer to: [1] sa Shixuan Wang Shan's introduction to Database system (second Edition) higher Education Press 1991, [2] [USA] Steven M.Bobrowski Oracle 7 and client / Server Computing Technology from introduction to proficiency in Liu Jianyuan, etc. Electronic Industry Press, 1996, [3] Zhou Zhongyuan Information system Modeling methods (part two) Electronics and Informatization, No. 3, 1999

Imagine the future, but don't forget the lessons of the past.

I find it useful to ask users what they think of future demand changes. This can achieve two purposes: first, you can clearly understand where the application design should be more flexible and how to avoid performance bottlenecks; second, you know that users will be as surprised as you when there is an undetermined change in requirements.

Be sure to remember the lessons of the past! We developers should also help each other by sharing our own experiences and experiences. Even if users think they don't need any support anymore, we should educate them about it. We've all been faced with a moment when "I wish I had done this."

Logical design before physical practice

Logical design should be carried out before going deep into physical design. With the emergence of a large number of CASE tools, your design can also achieve a fairly high logical level, and you can usually have a better understanding of all aspects of database design as a whole.

Know your business.

Don't add a single datasheet to your ER schema until you are 100% sure that the system meets its needs from the customer's point of view (what, you don't have a schema yet? Then please see Tip 9). Knowing your enterprise business can save a lot of time in the later development phase. Once you have identified your business needs, you can make many decisions on your own.

Once you think you have identified the business content, you'd better have a systematic communication with the customer. Use the customer's terminology and explain to them what you think and what you hear. At the same time, the relational cardinal number of the system should be expressed in terms of possibility, will and necessity. In this way, you can ask your customers to correct your own understanding and do the next step of ER design.

Create data dictionaries and ER charts

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

The importance of having an up-to-date document such as an ER chart cannot be overemphasized, which is useful to show the relationships between tables, while the data dictionary describes the purpose of each field and any aliases that may exist. This is absolutely necessary for the documentation of SQL expressions.

Create a pattern

A chart is worth a thousand words: developers should not only read and implement it, but also use it to help them talk to users. The pattern helps to improve the efficiency of collaboration, so it is almost impossible to have major problems in the previous database design. The pattern doesn't have to be complicated; it can even be so simple that it can be written on a piece of paper. We just need to ensure that the logical relationship on it can produce benefits in the future.

Start with input and output

When defining database table and field requirements (inputs), you should first examine existing or designed reports, queries, and views (outputs) to determine which tables and fields are necessary to support these outputs. Take a simple example: if a customer needs a report sorted, segmented, and summed by zip code, make sure it includes a separate zip code field instead of blending the zip code into the address field.

Report skills

To understand how users usually report data: batch processing or online report submission? Is the interval daily, weekly, monthly, quarterly, or annual? You can also consider creating a summary table if necessary. System-generated primary keys are difficult to manage in reports. Users often return a lot of duplicate data when retrieving with secondary keys in a table with a system-generated primary key. This kind of retrieval performance is relatively low and easy to cause confusion.

Understand customer needs

This should seem obvious, but the demand comes from the customer (here from the perspective of internal and external customers). Don't rely on the requirements written down by the user, the real demand is in the customer's mind. You need to ask the customer to explain their requirements, and as the development continues, often ask the customer to make sure that their requirements are still in the development purpose. An immutable truth is that "I don't know what I want until I see it" will inevitably lead to a lot of rework because the database does not meet the requirements that customers have never written down. To make matters worse, your interpretation of their needs only belongs to you, and it may be completely wrong.

Part 2-Design tables and fields

Check for changes

When I design the database, I consider which data fields may change in the future. For example, the surname is like this (note that it is the surname of Westerners, such as the surname of a woman after marriage). Therefore, when setting up a system to store customer information, I tend to store the last name field in a separate data table, with fields such as the start date and the end date attached, so that I can track changes in this data entry.

Use meaningful field names

I once worked on a project in which there was a program inherited from other programmers who liked to name fields with on-screen data indicating terms, which was not bad, but unfortunately she also liked to use some strange naming methods that adopted a combination of Hungarian naming and control serial numbers, such as cbo1, txt2, txt2_b, and so on.

Unless you are using a system that only targets your abbreviated field name, please describe the field as clearly as possible. Of course, don't overdo it, such as Customer_Shipping_Address_Street_Line_1, although it is very illustrative, but no one wants to type such a long name, the specific scale is in your grasp.

Named with prefix

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

Timeliness data should include the Last Updated date / time field. Time stamps are particularly useful for finding the cause of data problems, reprocessing / reloading data by date, and purging old data.

Standardization and data-driven

The standardization of data is convenient not only for yourself but also for others. For example, if your user interface accesses external data sources (files, XML documents, other databases, etc.), you might as well store the corresponding connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflow (sending mail, printing letterhead, changing record status, etc.), the data that produces the workflow can also be stored in the database. Pre-scheduling always takes effort, but if these processes are data-driven rather than hard-coded, then policy change and maintenance will be much easier. In fact, if the process is data-driven, you can put considerable responsibility on the user to maintain their own workflow process.

Standardization should not go too far

For those unfamiliar with the term normalization, standardization ensures that the fields in the table are the most basic elements, and this measure helps to eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to achieve the best balance in terms of performance, scalability, and data integrity. To put it simply, the 3NF stipulates:

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

* each row in 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.

Databases that comply with 3NF standards have the following characteristics: there is a set of tables dedicated to storing associated data connected by keys. For example, a 3NF database that holds customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the customer associated with the order, but the table holds a key value that points to the row in the Customer table that contains the customer information.

There is also a higher level of standardization, but is it necessarily better if it is more standard? The answer is not necessarily. In fact, for some projects, even 3NF can introduce too much complexity to the database.

There are many examples that it is sometimes necessary not to standardize tables for the sake of efficiency. Once upon a time, the job of developing food and beverage analysis software was to use non-standardized tables to reduce the average query time from 40 seconds to about two seconds. Although I have to do this, I will never take the non-standardization of datasheets as a natural design concept. And the specific operation is just a derivative. So if there is something wrong with the table, it is entirely possible to recreate a non-standardized table.

Skills of Microsoft Visual FoxPro report

If you are using Microsoft Visual FoxPro, you can use user-friendly field names instead of numbered names, such as Customer Name instead of txtCNaM. In this way, when you create forms and reports with the wizard [Wizards, called 'genie' in Taiwan], their names will make it easier for those who are not programmers to read.

Indicators that are inactive or not used

It is useful to add a field to indicate whether the record is no longer active in the business. Whether it's customers, employees, or anyone else, this can help filter out active or inactive status when you run the query again. At the same time, it also eliminates some of the problems faced by new users when adopting data, for example, some records may no longer be used by them, and can play a preventive role when they are deleted.

Use role entities to define columns [fields] that belong to a category

When you need to define things that belong to a specific category or have a specific role, you can use role entities to create specific time relationships, so that you can achieve self-documentation.

The implication here is not to have PERSON entities with Title fields, but rather, why not use PERSON entities and PERSON_TYPE entities to describe people? For example, when John Smith and Engineer are promoted to John Smith, Director and finally climb to the high position of John Smith CIO, all you have to do is change the key value of the relationship between the two tables PERSON and PERSON_TYPE, while adding a date / time field to know when the change occurred. In this way, your PERSON_TYPE table contains all the possible types of PERSON, such as Associate, Engineer, Director, CIO, or CEO.

Another alternative is to change the PERSON record to reflect the change in the new title, but it is impossible to track the exact time of a person's location.

Use common entities to name organization data

The easiest way to organize data is to use common names such as PERSON, ORGANIZATION, ADDRESS, PHONE, and so on. When you combine these commonly used general names or create specific sub-entities, you get a special version for your own use. The main reason for the use of general terms at the beginning is that all concrete users can materialize abstract things.

With these abstract representations, you can use your own special name in the level 2 logo. For example, PERSON may be Employee, Spouse, Patient, Client, Customer, Vendor, or Teacher. Similarly, ORGANIZATION may also be MyCompany, MyDepartment, Competitor, Hospital, Warehouse, Government, etc. Finally, ADDRESS can be Site, Location, Home, Work, Client, Vendor, Corporate, FieldOffice and so on.

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

Users come from all over the world

When designing a database that uses the Internet or other international characteristics, it is important to keep in mind that most countries have different field formats, such as postal codes, and some countries, such as New Zealand, do not have postal codes.

Data duplication requires separate data tables

If you find yourself re-entering data, create a new table and a new relationship.

Three useful fields that should be added to each table

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

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

* nRecordVersion, the version mark of the record; helps to accurately indicate the reason for the occurrence or loss of null data in the record

Use multiple fields for addresses and phones

It is not enough to describe the street address with just one line of record. Address_Line1, Address_Line2, and Address_Line3 can provide more flexibility. Also, it is best to have your own data sheet for phone numbers and email addresses, with their own types and tag categories.

Be careful with excessive standardization, which can lead to performance problems. Although address and phone table separation usually works best, if you need to access this information frequently, it may be more appropriate to store "preferred" information (such as Customer, etc.) in its parent table. The compromise between non-standardization and accelerated access makes sense.

Use multiple name fields

I was surprised that many people left a field for name in the database. I think only beginner developers would do this, but it's actually very common online. I suggest that you treat the last name and first name as two fields, and then combine them when querying.

What I most often use is to create a calculated column [field] in the same table, through which the standardized field can be automatically joined so that it changes as the data changes. However, this has to be very clever when using modeling software. In short, the use of connection fields can effectively isolate the user application and the developer interface.

Beware of mixed case object names and special characters

One of the things that annoyed me most in the past was that there were mixed case object names in the database, such as CustomerData. This problem exists from Access to Oracle databases. I don't like this mixed-case method of naming objects, and as a result, I have to change the name manually. Think about it, can this kind of database / application mix to the day when a more powerful database is adopted? Names that are all uppercase and contain underscores have better readability (CUSTOMER_DATA), and never leave spaces between the characters of the object name.

Be careful to keep words

Make sure your field name doesn't conflict with reserved words, database systems, or common access methods. For example, an ODBC linker I wrote recently has a table in which DESC is used as the description field name. You can imagine the consequences! DESC is a reserved word after the abbreviation of DESCENDING. A SELECT * statement in the table works, but what I get is a lot of useless information.

Maintain consistency of field names and types

Be sure to ensure consistency when naming fields and assigning them data types. If the field is called "agreement_number" in one table, don't change the name to "ref1" in another table. If the data type is an integer in one table, don't become a character in another table. Remember, you've done your job, and other people have to use your database.

Choose the number type carefully

Be very careful when using the smallint and tinyint types in SQL. For example, if you want to see monthly sales and your total field type is smallint, you cannot calculate if the total exceeds $32767.

Delete tag

Include a Delete tag field in the table so that rows can be marked for deletion. Do not delete a row individually in a relational database; it is best to use a clean data program and carefully maintain the integrity of the index.

Avoid using triggers

The function of triggers can usually be implemented in other ways. Triggers can become interference when debugging a program. If you do need to use a trigger, you'd better focus on documenting it.

Include version mechanism

It is recommended that you introduce a version control mechanism into the database to determine the version of the database in use. You must fulfill this requirement no matter what. Over time, the needs of users will always change. Eventually, the database structure may be required to be modified. Although you can determine the version of the database structure by checking new fields or indexes, I find it more convenient to store the version information directly in the database?

Leave enough room for the text field

Text fields of ID type, such as customer ID or order number, etc., should be set larger than usual, because you will probably be embarrassed by adding extra characters in a short time. For example, suppose your client's ID is 10 digits long. Then you should set the length of the database table field to 12 or 13 characters. Is this a waste of space? A little bit, but not as much as you might think: a field lengthened by three characters would only take up more 3MB space for the entire database with 1 million records and a little index. However, this extra space can increase the size of the database without having to reconstruct the entire database in the future. The number of ID cards changed from 15 digits to 18 digits is the best and most painful example.

Naming skills of column [field]

We found that if you use a uniform prefix for the column [field] names of each table, it will be greatly simplified when writing SQL expressions. This does have drawbacks, such as undermining the power of automatic table join tools, which associate public column [field] names with certain databases, but even these tools sometimes have incorrect connections. To take a simple example, suppose there are two tables:

Customer and Order. The prefix of the Customer table is cu_, so the subsegment names in the table are as follows: cu_name_id, cu_surname, cu_initials, cu_address, and so on. The prefix of the Order table is or_, so the subsegment name is:

Or_order_id, or_cust_name_id, or_quantity and or_description, etc.

So the SQL statement that selects all the data from the database 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, it is written like this (distinguished by aliases):

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

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

There are not many characters missing in the first SQL statement. But if the query involves five tables or more columns [fields], you know how useful this technique is.

Part 3-Select keys and indexes

Data mining should be planned in advance

At one point, one of my customer departments had to deal with more than 80,000 contact information and fill in the necessary data for each customer (this is definitely no small job). I also have to identify a group of customers as market targets. When I designed tables and fields from the beginning, I tried not to add too many fields to the main index to speed up the database. Then I realized that specific group queries and information mining were neither accurate nor fast. As a result, the data fields are rebuilt and merged in the primary index. I find that an instruction plan is quite critical-why use the number 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 as unimportant to me as the system type. Using the latter as the main field, re-indexing and retrieval are much faster after the database is updated.

There are differences in data indexing between operational data warehouse (ODS) and data warehouse (DW). In the DW environment, you have to consider how the sales department organizes sales activities. They are not database administrators, but they determine the key information in the table. Here the designer or database staff should analyze the database structure to determine the best conditions between performance and correct output.

Use a system-generated primary key

This is the same as technique 1, but I think it is necessary to remind you here. If you always use system-generated keys as primary keys when designing a database, 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: when you have a consistent key structure, it's easy to find logical flaws.

Decompose fields for indexing

To separate named and included fields to support user-defined reports, consider decomposing other fields (or even primary keys) as their constituent elements so that users can index them. Indexing speeds up the execution of SQL and report builder scripts. For example, I usually create reports where I have to use SQL LIKE expressions, because case number fields cannot be broken down into elements such as year, serial number, case type, and defendant code. The performance will also deteriorate. If the year and type fields could be broken down into index fields, these reports would run much faster.

Four principles of key design

* create a foreign key for the associated field.

* all keys must be unique.

* avoid using compound keys.

* Foreign keys are always associated with unique key fields.

Don't forget to index.

Indexing is one of the most efficient ways to get data from a database. 95% of the database performance problems can be solved by index technology. As a rule, I usually use a unique grouped index on the logical primary key, a unique non-grouped index on the system key (as a stored procedure), and a non-grouped index on any foreign key column [field]. However, the index is like salt, too much food will be salty. You have to consider how much space the database has, how the tables are accessed, and whether these accesses are mainly used for reading and writing.

Most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used keys, such as running a query to show a record of the main table and all associated tables. Also, do not index memo/note fields, do not index large fields (with many characters), this will cause the index to take up too much storage space.

Do not index commonly used small tables

Do not set any keys for small data tables, especially if they often have insert and delete operations. Index maintenance for these insert and delete operations may take more time than scanning tablespaces.

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

Never use SSN or ID as the key to the database. In addition to privacy reasons, it is important to note that the government increasingly tends not to allow SSN or ID to be used for purposes other than revenue-related purposes, and SSN or ID need to be entered manually. Never use a manually typed key as the primary key, because if you type the wrong key, the only thing you can do is to delete the entire record and start from scratch.

When I was cracking other people's programs, I saw a lot of people using SSN or ID as serial numbers, even though it was illegal to do so. And people know it's illegal, but they're used to it. Later, with the increase in identity theft crimes, my current colleagues are painfully removing SSN or ID from a large pool of data.

Do not use the user's key

When determining which field to use as the key for the table, be sure to be careful about the field the user is going to edit. In general, do not select a user-editable field as the key. Doing so will force you to take the following two steps:

* impose restrictions on the user's behavior of editing fields after the record is created. If you do this, you may find that your application suddenly changes in business requirements, and users lack enough flexibility to edit non-editable fields. What do users think when they enter data and don't find out that there is something wrong with the system until they save the record? Delete and rebuild? Do you want the user to walk away if the record cannot be rebuilt?

* propose some methods to detect and correct key conflicts. Usually, it takes a little effort to get it done, but it's more expensive in terms of performance. Also, key correction may force you to break through the isolation between your data and the business / user interface layer.

So repeat the old saying: your design should adapt to the user, not let the user adapt to your design.

The reason why primary keys are not updatable is that in relational mode, primary keys implement associations between different tables. For example, the Customer table has a primary key CustomerID, while the customer's order is stored in another table. The primary key of the Order table may be OrderNo or a combination of OrderNo, CustomerID, and date. No matter which key setting you choose, you need to store the CustomerID in the Order table 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, you must find all the relevant records in the Order table and modify it. Otherwise, some orders will not belong to any customer-the integrity of the database will be ruined.

If index integrity rules are imposed at the table level, it is almost impossible to change the keys of a record and all associated records in the database without writing a lot of code and additional deletion records. And this process is often full of mistakes, so it should be avoided as much as possible.

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

Remember, it is not machines but people who query data.

If you have an optional key, you may further use it as the primary key. In that case, you have the ability to build a strong index. This prevents people who use the database from having to connect to the database to filter the data properly. On databases that strictly control domain tables, this load is eye-catching. If the optional key is really useful, it is at the level of the primary key.

In my opinion, if you have optional keys, such as state_code in the country table, you should not create subsequent keys on existing unique keys that cannot be changed. All you have to do is create worthless data. If you overuse the subsequent key [alias] of the table to establish this kind of table association, the operation load really needs to be considered.

Don't forget the foreign key.

The primary key field that is automatically created by most database indexes. But don't forget to index foreign key fields, which are used every time you want to query records in the main table and their associated records. Also, do not index memo/notes fields and do not index large text fields (many characters), which will make your index take up a lot of database space.

Part 4-ensuring the integrity of data

Enforce data integrity with constraints rather than business rules

If you deal with requirements according to business rules, you should check the business level / user interface: if the business rules change later, you only need to update them. If the requirements arise from the need to maintain data integrity, then restrictions need to be imposed at the database level. If you do use constraints in the data layer, you need to make sure that there is a way to inform the user interface why updates fail constraint checking in a language that the user understands. Unless your field name is lengthy, the field name itself is not enough.

Whenever possible, use a database system to achieve data integrity. This includes not only the integrity achieved through standardization but also the functionality of the data. Triggers can also be added when writing data to ensure the correctness of the data. Do not rely on the business layer to ensure data integrity; it does not guarantee the integrity between tables (foreign keys) so it cannot be imposed on other integrity rules.

Distributed data system

For distributed systems, you should estimate the amount of data over the next 5 or 10 years before you decide whether to replicate all the data at each site or keep it in one place. When you transfer data to other sites, it is best to set some tags in the database field. Update your tags after your data is received at the destination site. For this kind of data transfer, please write down your own batch or scheduler to run at specific intervals instead of letting users transfer data after daily work. Copy your maintenance data locally, such as calculating constants and interest rates, and set the version number to ensure that the data is exactly the same at each site.

Mandatory indication of integrity (referential integrity?)

There is no good way to eliminate harmful data after it enters the database, so you should remove it before it enters the database. Activate the indicated integrity feature of the database system. This keeps the data clean and forces developers to spend more time dealing with error conditions.

Relationship

If there is a many-to-one relationship between two entities, and it is possible to turn into a many-to-many relationship, you'd better set it up as a many-to-many relationship in the first place. It is much more difficult to move from an existing many-to-one relationship to a many-to-many relationship than to a many-to-many relationship from the beginning.

Adopt view

To provide another layer of abstraction between your database and your application code, you can create a special view for your application without requiring the application to access the data table directly. This also gives you more freedom when dealing with database changes.

Plan for data retention and recovery

Consider the data retention strategy and include it in the design process and design your data recovery process in advance. Use a data dictionary that can be published to users / developers to achieve convenient data identification while ensuring that the data source is documented. Write an online update to "update the query" so that you can reprocess the update later in case data is lost.

Use stored procedures to make the system do heavy work

After solving many troubles to produce a highly complete database solution, I decided to encapsulate some functional groups of associated tables and provide a set of regular stored procedures to access each group to speed up and simplify the development of client code. A database is not only a place to store data, it is also a place to simplify coding.

Use find

The best way to control data integrity is to limit users' choices. Whenever possible, users should be provided with a clear list of values to choose from. This will reduce errors and misunderstandings in typing code while providing data consistency. Some public data are particularly suitable for finding: country codes, status codes, and so on.

Part 5-Tips

Document, document

Document all shortcuts, naming conventions, restrictions, and functions.

Use database tools that annotate tables, columns [fields], triggers, etc. Yes, it's a bit cumbersome, but in the long run, it's very useful for developing, supporting, and tracking changes.

Depending on the database system you use, there may be some software that will give you some documents that you can get started with quickly. You may want to start talking and then get more and more details. Or you may want to schedule periodically, entering new data and detailing each part as you progress. No matter which way you choose, always document your database, either inside the database itself or separately. In this way, when you go back to version 2 after more than a year, your chances of making mistakes will be greatly reduced.

Use common English (or any other language) instead of coding

Why do we often use coding (for example, 9935A may be the supply code for 'Tsing Tao Beer' and 4XF788-Q may be the account code)? There are many reasons. But users usually think in English instead of coding. Accountants who have worked for 5 years may know what an 4XF788-Q is, but newcomers are not necessarily. It is best to sort by English name when creating drop-down menus, lists, and reports. If you need to code, you can attach the English that the user knows next to the code.

Save commonly used information

It is useful to have a table dedicated to general database information. I often store the current version of the database, recent checks / fixes (for FoxPro), the name of the associated design document, customers, and so on, in this table. This implements a simple mechanism for tracking databases, which is especially useful for non-client / server environments when customers contact you when they complain that their database does not meet the desired requirements.

Test, test, repeat test

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

Check the design

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

Design skills of Microsoft Visual FoxPro

For complex Microsoft Visual FoxPro database applications, you can put all the main tables in a database container file, and then add other database table files and load special files related to the original database. Use these files to connect to the master table in the master file as needed. Such as data input, data indexing, statistical analysis, providing reports to management or government departments, and all kinds of read-only queries. This measure simplifies the allocation of user and group permissions, and facilitates the grouping and partition of application functions (stored procedures), making it easy to manage when the program must be modified.

This is the end of the content of "what are the parts of database design". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.