Docs‎ > ‎Logic Designer‎ > ‎Database‎ > ‎

phpMyAdmin for MySQL

If you are comfortable with client development but a bit new to SQL, use this page as a quick-start for MySQL.

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

Creating your Database Server

The operations of creating tables, columns and relationships is described in the following sections.  These all occur in the context of a database.

You can create your own database, either in the cloud or on-premise.  Even easier, you can request a database be created automatically when you create your account.

phpMyAdmin

phpMyAdmin is a web-based utility to manage data and schema.  You can use 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.

Schema - define 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

A reasonable way to begin database design is to consider your JSON, here a Customer and some Orders (just one Order shown for brevity):

[
   {
      "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, there are two key concepts illustrated here:
  1. Table - an object, here Customer, Order

  2. Column - field of an object, here Name, Balance, Order Number, etc.  
To build this with phpMyAdmin, first create the table if it doesn't already exist:

and then press the Insert button to create columns like this:


Keys

Relational databases provide lots of options and power for indices and keys:
  • a index on 1 / several columns can dramatically improve performance

  • you can designate an index as unique (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, below:



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.  In Espresso, we informally call these the 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.

To define a Foreign Key Relationship:
  1. Create an index on the Child Column(s) that comprise the Foreign Key

  2. Create the Relationship as shown below.  Note there is substantial power in the Cascade rules.
Create the Relationship by selecting the Child Table > Structure > Relation View:


Then select the Foreign Key fields and complete the form as shown below:

Notes:
  1. The entry 'val2_dem'.'customer'.'name' is the Parent's Primary Key Fields

  2. Be sure to specify the Constraint Name.  It needs to be unique across your schema.  A good default is to use the name of the Parent Table, but give consideration to these additional considerations.