Docs‎ > ‎Integrate Menu‎ > ‎Data Sources‎ > ‎

phpMyAdmin for MySQL

Use this page as a quick-start for MySQL. phpMyAdmin is a web-based utility to manage data and schema. You can use this utility instead of using DDL (Data Definition Language - a syntax for defining a schema) and DML (Data Manipulation Language - the syntax for creating, reading, updating and deleting data). A link was provided in your registration email.

This following description relies on examples from the simple Demo Database, which is installed automatically so you can easily explore it.

Prerequisite: You are comfortable with client development but a bit new to SQL.

Create your Database Server

The following sections describe how to create tables, columns, and relationships in the context of a database. You can create your own database, either in the cloud or on-premises. Or you can request a database be created automatically when you create your account.

Schema - Define the Database Structure

Relational databases require that you define the structure of your database in what is commonly called a schema.  Here you define the tables (objects) and columns (attributes) that comprise your database.

Excel Spreadsheet -> Table

You can follow this procedure to create a table from a spreadsheet.  You can create multiple tables using this technique, then relate them together by defining relationships.

JSON Object -> Table

You can begin database design by considering your JSON, in this example, a Customer and some Orders. The following code snippet shows one order as an example:
[
   {
      "name":"Alpha and Sons",
      "balance":125,
      "credit_limit":901,
      "Orders":[
         {
            "order_number":1,
            "amount_total":75,
            "paid":false,
            "notes":"This is a small order"
         }
      ]
   }
]

In database parlance, the following key concepts are illustrated:
  • Table - an object, in this example Customer and Order
  • Column - field of an object, in this example Name, Balance, Order Number, etc.
To build this with phpMyAdmin, complete the following:
  1. Create the table if it doesn't already exist:
  2. Click Insert to create columns like this:

Keys

Relational databases provide many options and power for indices and keys, including:
  • An index on one/several columns, which can improve performance.
  • An index you can designate as unique. Note: Duplicates are rejected at runtime. The primary key is the main unique index.

Auto_Increment Primary Keys

Many database designers elect to define a column (often called Id) as a system-computed AUTO_INCREMENT. This is also called a surrogate key (as distinct from a natural key). This can simplify your database design, as shown for Purchaseorder in the following image:

JSON SubObject -> Foreign Key Relationship

The indentation from Customer -> Order represents a one-to-many Relationship. This is directly supported by the DBMS - you can define it, along with actions that govern changes. These are also referred to as Parent and Child tables.

Database developers often draw diagrams as shown on the right to document their database structure.  It's a great idea.  The lines represent the Relationships, where the fork is attached to the child.

You define a Relationship by defining a Foreign Key, wherein the child contains the Primary Key of the Parent.  In our example, the Order (child) contains a field (customer_name) that matches the Primary Key of the Customer (parent), the name.

Define a Foreign Key Relationship

  1. Create an index on the Child Column(s) that comprise the foreign key. The entry 'val2_dem'.'customer'.'name' is the parent's primary key field.
  2. Create the Relationship.  Note: The cascade rules provide power. Create the relationship by selecting the Child Table, Structure, Relation view:
  3. Select the Foreign Key fields and complete the form. The following image shows an example of the form. Specify a unique constraint name across your schema. We recommend using the name of the parent table. For considerations in determining role names, see Data Sources.
ċ
phpAutoIncr.png
(262k)
Michael Holleran,
Feb 16, 2016, 2:04 PM
ċ
phpCustomer.png
(225k)
Michael Holleran,
Feb 16, 2016, 2:04 PM
ċ
phpIndex.png
(182k)
Michael Holleran,
Feb 16, 2016, 2:04 PM
ċ
phpOrders.png
(291k)
Michael Holleran,
Feb 16, 2016, 2:04 PM
ċ
phpRelationships.png
(254k)
Michael Holleran,
Feb 16, 2016, 2:04 PM
ċ
phpTables.png
(236k)
Michael Holleran,
Feb 16, 2016, 2:04 PM
Comments