When designing a database, follow the rules described below.
Avoid duplicating data.
There is no need to store information in an attribute if it is available in another attribute in this file or another file. For example, an Invoice file may not need to keep customer name, address and telephone number attributes. All of these attributes are available from the Customer file. Keeping this information in a separate file reduces the amount of disk space and only requires one update to change the customer information.
Derive values from existing attributes or translate them from other files whenever possible.
Often, attributes contain information that is readily retrieved from other items, such as an invoice containing a part number description (which should be available from the part number file), or an invoice total (which can be calculated from the list of invoice prices and quantities). The goal here is to create a dictionary attribute-defining item that refers to the data that exists elsewhere and returns it in the required format. The actual value need not reside in this attribute.
Keep attribute data homogeneous.
Use an attribute for one and only one type of data. Never combine two or more data types into a single attribute. If two pieces of information go together, create another attribute to keep track of the second part. The controlling/dependent structure can be used to link them together. When storing numbers with an embedded decimal position, use the Mrxy processing code to normalize the stored data. If text is always displayed as uppercase, use the mcu processing code on input to convert to uppercase before storing the data.
Store data in the internal format.
Attributes such as dates and times should be stored in the internal format. The internal format is an integer number that represents the number of days before or after December 31, 1967. Dates plus or minus 200 years only require storage of up to five digits. Internal format values can be sorted more quickly or compared against other values than their external companions.
Store numeric values in the normalized internal integer format. This is especially important when using AQL, because numbers that are stored in a noninternal format do not sort, select, or total correctly.
Store text attributes in the format in which they are usually displayed. When necessary, use processing codes to normalize data prior to sorting.
Telephone numbers, social security numbers, zip codes, and other numbers that contain specific patterns can be stored without the pattern delimiters; a simple processing code restores the pattern before output.
In short, using internal formats compresses data and maintains consistent data form.
Keep attributes and items small.
To reduce record size even further, keep the size and number of attributes to a minimum. Use processing codes to reduce the size of the data stored. Consider creating a new file to place rarely used attribute values and use the same item-ID as in the original file for speedy access. Store historical data in another file (another data section of the same file, such as sales,sales and sales,history). D3 performs best if the item size is kept below 1720 bytes.
Arrange attributes in order of importance and relative size.
With D3, attributes do not have to be in any particular order. However, planning of the attribute order makes files more efficient and programs (AQL as well as FlashBASIC) run faster. Generally, the rule is to organize attributes in descending order of importance, reading often used attribute values quickly, and in ascending order of average attribute size so time is not wasted skipping over long attribute values to get to the desired attributes.
By efficiently using D3 file structures, applications are written in less time, data occupies less disk space, programs run faster, and changes are made more easily.