DataPal web setup procedures

Setting up DataPal with MySQl and PostgreSQL database(s) is only several easy steps away:

1. Create database(s) and tables that you want DataPal to manage
2. Create logins and issue SELECT, INSERT, UPDATE, DELETE permissions for the database logins.
3. Upzip DataPal and upload it to PHP enabled yourWebServer.com to a separate directory (referred as uploadDirectory below) on your web server.
4. Open uploadDirectory/classes/DBSelector.php and add your database(s) connection information to it.
5. Take your browser to http:/www.yourWebServer.com/uploadDirectory/ to manage your database(s) data online.

Database design and naming rules

Naming convention is used by online data automation to determine primary and forein keys, mark many to many bridge tables, determine menu nesting and keep it consistent across different database engines.

1. Please use _ (underscore) in tables and columns names to have it displayed as a white space. For example when table name needs to be displayed as 'Customer Addresses' it should be named 'Customer_Addresses'.
2. Primary key must be present in all database tables. Primary key must be named uppercase ID. System automation will mark all fields named ID as primary keys without sending compicated queries to database, this helps keep DataPal lightweight and cross database compatible.
3. Foreign key name must be the name of the table that it refers to plus uppercase ID. For instance when table 'Addresses' needs a foreign key pointing to 'Customers' table, we should name the foreign key CustomersID. When table field name ends with ID, DataPal automated data manager will mark this field as a foreign key and look up it's related record in parent table based on the naming convention. Please do not have table field name end with ID if it is not a foreign keys. Foreign key must be of the same datatype as a primary key.
4. Many to Many intermediary bridge tables names should start 'm2m'. Many to Many relationships are supported between 2 tables at a time. Many to Many intermediary table should contain 3 columns only: 'ID' which is a primary key, 'Table1ID' column pointing to primary key in 'Table1' and 'Table2ID' key pointing to primary key in 'Table2'. All keys should be of the same datatype as a primary key datatype.

Interfacing Databases with DataPal - sample database scripts with live DataPal demos

• Address Book
• Data Types
• One to Many
• Many to Many

Address Book - live demo - Let's start with the simplest example of DataPal use - create a simple address book online. For demonstration purposes we created one MySQL and one PostgreQSL databases, both of them have a similar structured table called 'Addresses'.

1. Create database table
You can create both databases with 'Addresses' table or just any one of them.
SQL script that creates 'Addresses' table for MySQL database is here
SQL script that creates 'Addresses' table for PostgreSQL database is here

DBSelector.php entries for both MySQL and PostGreSQL databases registration is here

Data Types - live demo - online application demonstrates how online automation translates differend database datatypes into web form fields.

1. Create database table
You can create both databases with 'Data_Types' table or just any one of them.
SQL script that creates 'Data_Types' table for MySQL database is here
SQL script that creates 'Data_Types' table for PostgreSQL database is here

DBSelector.php entries for both MySQL and PostGreSQL databases registration is here

One to Many relationships - live demo - building and managing one to many relationships between tables with online application. We have 'Customers' table, each record in 'Customers' can have unlimited number of records in addresses table.

1. Create database tables
You can create both databases with 'Customers' and 'Addresses' tables or you can create just one database of your choice with 'Customers' and 'Addresses' tables.
SQL script that creates tables for MySQL database is here
SQL script that creates tables for PostgreSQL database is here

DBSelector.php entries are edited similar to the exapmles above.

Tip: Customer addresses (on the 'many' side) could be viewed on customer entry 'view' page on the bottom. To assign an additional address to a Customer create a new address and in foregn key drop down select availabe customer entries, or use an existing address, go to 'Edit' page and select a different customer from foregn key drop down, then save.
On the address 'view' page related customer name on the 'one' side could be viewed next to foregn key entry - i.e CustomersID 1 -> Scott. Scott entry is the second field in 'Customers' table definition, it is recommended to put most descriptive table row field in a second place in database table definition as it used to get general row description in 'many to one' lookups and also on search pages when tables have more than 4 fields defined.

Many to Many relationships - live demo - building and managing many to many relationships between tables with online application. We have 'Products' and 'Categories' table, each record in 'Products' can have any number of records in 'Categories' table, and each record in 'Categories' table can have any number of records in 'Products' table.
Many to many relationship is done via third intermediary table that contains links to primary keys of related tables. DataPal will mark any table which name starts with 'm2m' as many to many intermediary table. You can name this table 'm2m_Plus_anything', naming of the fields inside the table will point DataPal to correct tables that should be linked together. Naming convention for intermediary table keys as follows:
ID - is it's own primary key that must be present.
'Table1ID' - key that points to primary key in 'Table1'
'Table2ID' - key that points to primary key in 'Table2'
Automated many to many relationship is supported between 2 tables at a time only. You will never get to see 'm2m' tables directly in Datapal - it manages 'm2m' tables automatically when many to many relatives are assigned to record.

1. Create database tables
You can create both or any of the databases with 'Categories', 'Products' and 'm2mProductsCategories' tables.
SQL script that creates tables for MySQL database is here
SQL script that creates tables for PostgreSQL database is here

DBSelector.php entries are edited similar to the exapmles above.


Tip: To view existing relatives on either side go to view record page - all relatives are displayed on the bottom on a green background. Modify record page does not display relatives automatically, but it provides links to edit relatives page on the bottom on a green background. Clicking the link will open edit many to many relatives page with multiple checkboxes checked when record in another table is related to current record. Check or uncheck relatives as needed and click 'save' button on the top. Links to parent record are displayed on the top.