Tutorials to .com

Tutorials to .com » Dotnet » Database » Standardized database skills (MS)

Standardized database skills (MS)

Print View , by: iSee ,Total views: 6 ,Word Count: 2859 ,Date: Sun, 14 Jun 2009 Time: 2:47 PM

Standardized database skills

Luke Chung
FMS president
September 2002 applies to:

Microsoft ® access

Abstract: This paper provides developers with a number of techniques, the use of these techniques in the design of Access table can avoid certain problems. This article applies to Microsoft Access database (. Mdb) and Microsoft Access project (. Adp).

Contents

About understand your data you need, what kind of data?
How you plan to deal with these data?
How interrelated data?
With the passage of time data will be what changes take place?
Learn how to query the database using the concept of standardization will be the only information stored in a local record is free of charge, and the new understanding of the field is very expensive when there is no need to copy the data using the exact meaning of the field as a primary key field using the Referential Integrity Summary

Introduction in the design of the database, the most important steps is to ensure that the correct distribution of data to the database table. Use the correct data structure, can greatly simplify the application of other elements (queries, forms, reports, code, etc.). The right to carry out the design of the official name is "standardized database."

This paper introduces the basic concept of a standardized database, and some need to pay attention to and strive to avoid common problems.

Understanding in the design of your data table before you plan how to deal with specific data, but also to understand the data over time will change what happened. Your assumptions will affect the final design.

What you need data?
Design application, the key is to understand the final outcome of the design in order to ensure that you're ready all the necessary data and know its source. For example, the appearance of statements, each data source, as well as all the necessary data exist. The biggest loser of the project is late in the project found a lack of important data statements.

Know what kind of data it is necessary to identify the source of the data. Data from other data sources to import? The need for clean-up or data validation? The need for users to input data?

Clearly required for the type and source of data is the first step in database design.

How you plan to deal with these data?
Users need to edit the data? If necessary, how to display data in order to understand and edit the user? Is there any validation rules and associated look-up table? Edit and delete calls for retention of backup data input has not reviewed the associated problems? The need for users to indicate the types of summary information? Whether there is a need to generate an export file? Understand the information, on the field can imagine how between the interrelated.

How interrelated data?
Add the relevant data packet field (for example, customer-related information, and invoice-related information, etc.), each field represents the group to set up the table. And then consider how these tables are interrelated. For example, which has a one-to-many relationship between tables (for example, a customer may have multiple invoices)? What are the table with a one-on-one relationship (the circumstances will normally consider their portfolio to a table)?

With the passage of time data will be what changes take place?
After the design table, often not considered due to the effects of time lead to serious problems later. Many of the design used in the very good results, but often because users to modify data, add data, and over time, collapsed. Developers often find the need to redesign the structure to adapt to these changes. Changes in the structure, all relevant content (information, forms, reports, code, etc.) must also be updated. Understand and predict the data with which change over time, can achieve a better design, to reduce the problems.

Learn how to learn how to use the query data analysis and management is equally important. You should be a deep understanding of the working principle of inquiry, understanding how to use the query form in a number of links between the data, how to use the query data grouping and aggregation, as well as how to standardize the format does not need to display data using crosstab query.

Good data on the ultimate goal of design is to balance two needs: to make it effective with the passage of time to store data, but also easy to retrieve and analyze data. Understand the functions of query design table to the right helpful.

This part of the concept of a standardized database of databases and the standardization of the basic concepts involved, rather than a standardized database of theoretical. How the actual situation in your application of these concepts may be needed as applications vary. This part of the aim is to understand these basic concepts, apply them according to actual needs, and understand there will be a departure from the concept of what these issues.

The only information will be stored in a database where the majority of developers to understand the basic concept of a standardized database. Ideally, you want the same data will be stored in the same place, and the need to invoke the use of ID for reference. Therefore, if certain information has changed, you can make changes in one place, and the entire process will also change the corresponding information.

For example, the customer table will store a record of each customer, including name, address, telephone number, email address, as well as other characteristics of information. Customer table may contain only the CustomerID field (usually the Autonumber field), this field of the primary key fields, other tables to use it to refer to the client. Therefore, the invoice table can be invoked only customer ID value, rather than stored in each customer's invoice all of the information (as with a client may hold more than one invoice), so use the ID value to customers from the customer table Find detailed information on customers. Access in the use of a powerful form (using the combo box and sub-form), can easily accomplish this task. If you need to modify customer information (such as new phone number), just modify the customer table, the application of the information cited in any other part of Automatic Updates will vanish.

Standardized using the correct database, a simple editor you can easily process data over time changes occur. The use of the database is not properly standardized, usually need to change programming or query to a number of records or more tables. This will not only increase the workload will increase as a result of the implementation of the code is not correct or inquiries which led to the possibility of data inconsistencies.

Record is free of charge, and very expensive new field should be the ideal database only with the passage of time to add new records, database tables should be able to save a large number of records. However, if you find the need to add more fields, you may run into design problems.

Spreadsheet experts often encounter these problems because they are accustomed to electronic form in accordance with the design of the database design. Design of regular time-varying field (for example, year, quarter, product and sales staff) need to add a new field in the future. And correct information should be designed to be converted to time-varying data in a field so that you can add more records. For example, simply create a "year" field, and then enter in the field of the record of the year corresponding to the value of the need for a year to create a separate field.

Additional fields may cause problems, because changes in the structure will form the application have an impact on other parts. In the table to add more fields, the dependence on the table of objects and code also needs to be updated. For example, the query need to acquire additional field, the form needs to display these fields, and the statements contained in these fields is required, and so on. However, if the data have been standardized, it will automatically retrieve the current object data and correctly calculate or display the data. In particular, the inquiry powerful because it allows you to press the "Year" field group to show year-on-year summary (no matter which table contains the year).

However, standardization does not mean that data can not show or use changes over time or depend on the time of the field. Need to browse or that such information is usually the developer can use the crosstab query to achieve this purpose. If you are not familiar with the crosstab query, it should be to learn how to use them. Although they vary with the form (in particular, the user can not edit the results of cross-table queries), but they really can be used in the data table to display information (up to 255 fields can be achieved). If you want to use them in the statements will be more complicated, because the need to include additional statements or the ever-changing field names. That is why the majority of statements, the data packet as an independent (not independent) for showing. For those who have no choice, you must take the time to solve this problem. I hope everyone can understand this decision will change over time the impact of other resources.

That is why the record is free of charge to increase (this is the great advantage of the database) and the increase in field is the reason so expensive. If the database design is correct, you can adapt to a variety of changes.

When it is necessary to copy the data to understand sometimes the need for anti-standardized data in order to save time may change.

Customer ID number through the link to the invoice to the customer table in a simple example, we may need to retain an invoice at the time of customer's address (rather than the address at the time of the production of invoices, as customer information in these two events might be change). If an invoice is not retained when the customer's address, and will also need to update customer information, you may not be able to send some invoices to determine the exact address. This may lead to very serious business issue. Of course, some information (such as the customer's phone number) can not save. Therefore, there should be options to decide what data you want to copy.

Need to copy the data to fill another example is the breakdown of the invoice. Quotations used in the selection of customers to order goods. We can only offer a single storage ID, and ID to include product description, price and other details of the quotations. However, the product description and price will change over time. If you do not copy data from the quotations to the schedule in the future can not be accurately re-print the original invoice. If you have not yet received payment, the problem will be very serious.

Thus, although standardization of the same data can be well preserved in one place and simplify the editing, but some cases do not need them. Due to historical reasons, if the future needs of the snapshot data, it must be from the very beginning in the database design. Otherwise, once the data is no longer on the back cover.

There is no precise meaning of the use of the field as a primary key field in order to improve efficiency, each table should have a primary key field. Primary key field in the table defines the uniqueness of the index used in other fields to improve search performance. For example, the customer table may contain only the definition for each customer number CustomerID field. In order to facilitate the discussion, assume that table contains a number of fields, not just a simple single-table to find (such as national / regional list).

In general, the primary key field should have the following characteristics:

Field should only contain a number of fields can be defined as primary key fields, but it is best to use a field. First of all, if you need to use multiple fields to define unique, you need to occupy more space to store the primary key. Secondly, other indexes in the table primary key must also use a combination of field, so that the space occupied by a field than the use of the space occupied by more. Final that in the table ID field need to acquire portfolio of records. CustomerID field using a definition of customers to use other field than the combination of much better.
Should be the number of types of
Access provided by an AutoNumber field type Long Integer (long integer), is applied to the primary key field. These values can be automatically guarantee the uniqueness of each record, as well as support for multi-user data entry.
Would not change over time should not be the primary key fields change over time. Once the identity of the primary key field, it should always be the same (as the same social security number). Changed the primary key field will be difficult to use historical data, because the link was destroyed.
There should be no precise meaning to ensure that the primary key field will not change over time, it should not be exactly what it means. There is no precise meaning of the main keys in the other data are not very useful when complete. For example, you can specify a customer number, without the full address of the client. The rest of the applications can work well, you can also add information retrieval records. If the table uses the country / region field or other fields you do not have the logo as part of primary key, then it is likely that the application can not be used.
For these reasons, we recommend that in most fields are used in the tables as the primary key AutoNumber field. By using the combo box and hide the column can be bound to AutoNumber fields and hidden fields, so that users can not see.

The use of referential integrity defined on the table and understand how the table is linked to, please add referential integrity to ensure the consolidation of the relationship between the tables. This will avoid errors and modify the link field of the record left in isolation. Microsoft Jet database engine to support the complex referential integrity, allowing users to cascade update and delete. Under normal circumstances, should not be altered ID field. Therefore, less use of cascading updates, but it is very useful for cascade delete.

For example, if the invoice table table associated with the orders, of which an invoice may be an unlimited number of orders (detailed above), and it contains records for each order of the invoice number link, you can use cascading delete operation to delete an invoice records, and automatically delete all records of the corresponding orders. This could have been avoided, there is no corresponding record of the invoice record orders.

Summary We hope that you can design them as soon as possible the concept of a database application to your application design, in order to minimize the problem, not the realization of such a design to reduce the need for amendments. Good luck.

Luke Chung is the FMS Inc.'s Founder and president. FMS Inc. Is the industry's leading supplier of third-party products, and its products for Microsoft Access users and developers.


.Net Database Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.