Creating a Database File, Table and Fields

Example

Creating a product database for a searchable online shopping basket.

MGI Tags

  • mgiEditDatabase

Steps

  1. Create a database field administration page in a text editor.
  2. Insert the mgiEditDatabase tag.
  3. Save the database field administration page.
  4. FTP the database field administration page to the web server running MGI.
  5. View the database field administration page in a browser.
  6. Enter the name of a new database file.
  7. Enter the name of a new database table.
  8. Add each database field.
  9. Populate the database table.

Step 1: Create a database field administration page in a text editor.

Create a new page in a text editing program to display the web-based, database administration interface.

Step 2: Insert the mgiEditDatabase tag.

On the database field administration page, enter the mgiEditDatabase tag, modifyTables parameter and modifyFields parameter. In the modifyTables parameter, enter "Yes" to indicate that you wish to create or modify database tables. In the modifyFields parameter, enter "Yes" to indicate that you wish to create or modify database fields. 
<mgiEditDatabase modifyTables="Yes" modifyFields="Yes">

Step 3: Save the database field administration page.

Save the database field administration page and name it "adminf.html".

Step 4: FTP the database field administration page to the web server running MGI.

Upload the database field administration page from your local computer to the web server using an FTP program.

Step 5: View the database field administration page in a browser.

View the database field administration page (adminf.html) in a browser. The first page of the web-based, administration interface is displayed.

Step 6: Enter the name of a new database file.

Select the radio button under the sentence "Or, enter the name for a new database (if it doesn't exist, it will be created):". In the text field, enter the name of a new database file (e.g., Clothing). Database names are case-sensitive. Click the "Select Database" button to create the new database file and proceed to the next screen of the administration interface. The new database file will be physically located at the same file level as the database field administration page.

Warning: Never FTP a database file or open the database file in a text editing program. The file type and creator code of the database may be changed and MGI will be unable to access or display information from the database.

Step 7: Enter the name of a new database table.

Select the radio button under the sentence "Or, enter the name for a new table (if it doesn't exist, it will be created): ". In the text field, enter the name of a new database table (e.g., Tshirts). Table names are case-sensitive. Click the "Select Table" button to create the new database table and proceed to the next screen of the administration interface. The new database table is created within the database file (e.g., Clothing).

Step 8: Add each database field.

In the final screen of the administration interface, enter a name, type and index (ordering) status for each database field. Before you enter the fields, consider what types of information you need in the database. Your database structure can be changed after you implement it, but it is far less time-consuming and frustrating to plan ahead for each piece of information.
 
For example, in an MGI database-driven shopping basket, you must provide at least 4 pieces of information for each product:
  • Product identification (field type: short text or long text). The shopping basket keys off of the product identification database field, therefore this field should be populated with a unique identification such as SKUs, ISBNs, etc.
  • Brief description of the product (field type: long text). The product's brief description appears in the shopping basket display and on the shopping basket order (in addition to any specific product descriptions from mgiPopup menus -- e.g. color, size, etc.), therefore this description should concisely identify the product However, you may have more than one description field; the brief description that appears in the shopping basket does not have to appear in the product display.
  • Price (field type: decimal number). Enter the price of each product in this field with two decimal places. Do not enter dollar signs or other characters.
  • Product's individual shipping cost (field type: decimal number). Choose the best shipping method for your products below. If you choose the BasePlusPerItem method, enter a shipping cost for each item in this field. If you choose any other method, you must enter a shipping cost field, but leave this field blank (i.e. 0.00).
 
Depending on the method of shipping you choose, additional database fields may be required. MGI has 4 methods of shipping:
  • BasePlusPerItem -- one base shipping charge per order and/or a shipping charge for each individual item. The BasePlusPerItem shipping method allows you to create 9 levels of price and shipping breaks for each item, therefore you may add up to 8 additional price quantity, price break, shipping quantity and shipping break fields (32 possible fields - the first level of price and shipping breaks is required for each item as described above). Give each field a unique name (e.g. pquant2, price2, squant2, ship2, pquant3, etc.). In the price quantity fields, enter the quantity at which price breaks occur. For example, if you have two price breaks for multiple purchases of a single item at a quantity of 10 and 20, then create two price quantity fields and enter "10" in the first field and "20" in the second field. In the price fields, enter the price of multiple purchases for the corresponding price quantity break. For example, if 9 or less of the same item are purchased, the price is $15.00 each, if 10 or more items are purchased, the price is $12.00 each, and if 20 or more items are purchased, the price is $10.00 each. In this example, enter $12.00 and $10.00 in the two additional price break fields. In the shipping quantity fields, enter the quantity at which shipping breaks occur and in the shipping break fields, enter the shipping cost of multiple purhcases of the item. All quantity fields should be whole number field types and all price fields should be decimal number field types.
  • Weight -- one shipping charge determined by the total weight of items in an order. The Weight method requires one decimal number database field for each item that lists the item's weight.
  • Percentage -- one shipping charge determined by a percentage of the subtotal. The Percentage shipping method does not require an additional database field.
  • TotalItems -- one shipping charge determined by the total quantity of items in an order. The TotalItems shipping method does not require an additional database field.
 
Once you have decided which fields to include in the database, consider the type of information that will be entered in each field. An MGI database field can contain the following types of information:
  • Whole Number (Integer): a whole number field can contain only whole numbers without decimals such as years, quantities, etc.
  • Decimal Number (Floating Point Number): a decimal number field can contain decimal numbers such as prices, weights, tax rates, percents, etc.
  • True/False (Boolean): a field containing the values "True" or "False" to discriminate records.
  • Short Text: a short text field can contain up to 250 alpha-numeric characters.
  • Long Text: a long text field can contain text that is greater than 250 alpha-numeric characters.
 
Finally, consider which fields may be used to order database search results. Products can be displayed in the order of entry (default), ascending order (A to Z and smallest to largest), or descending order (Z to A and largest to smallest). Any field that you may want to use for ordering search results must be indexed when you create the field. In the example Clothing database, the t-shirt design field will be indexed. Creating an indexed field does not predetermine the order of your products; it only allows you to order products by that criteria. The order of products is actually specified in the mgiSearchDatabase tag.
 
Create the folling fields for the example Clothing database are:
  • Design (short text, Indexed)
  • Description (long text)
  • Color (short text)
  • ProductID (short text)
  • Price (floating point)
  • Shipping (floating point)
  • Quantity2 (integer)
  • Price2 (floating point)
  • Quantity3 (integer)
  • Price3 (floating point)
  • Picture (short text)
  • DisplayTemplate (short text)
 
To create a field, enter the case-sensitive field name in the area labeled "Name". Select a field type from the pop-up menu labeled "Type". Select "True" beside the "Indexed" label if the field will be used to order database search results. Select "False" beside the "Indexed" label if the field will not be used to order database search results. Click the "Add Field" button to add the field to the new database table. Fields are listed in the order they are created below the new field interface.

Warning: Indexing fields adds overhead to the database processing, so only index fields that you need to use for ordering search results. Do not index every field! Indexing more than 5 fields may affect the performance of your database.

Step 9: Populate the database table.

The database creation process creates a database file, a table within the database file and fields within the table. To enter the information for each product into a database record, see the Database Population tutorial.

[Return to the Creating Databases Menu]

[User Guide Main Menu] [Understanding MGI Menu] [Using MGI Menu] [Referencing MGI Menu]