FourOh-LLC Welcome Wiki

Factory 4.0 Open Initiative, LLC.

User Tools

Site Tools


the_database:relational_sql

Relational SQL Database

GNU/Linux distributions have a 'default' choice for the SQL database. On Debian 9 that is MariaDB, while on other releases and distributions it might be MySQL or Percona. No other SQL database is part of the current FourOh-LLC Software Stack.

Process Basics

This is our super-duper highly complex diagram showing how web forms function:

SQl - Joomla! - Fabrik Web Forms The “light” SQL database holds the unfiltered data for everyone, about everything. In very lose terms this diagram explains:

  • The Fabrik framework creates the SQL filters to limit the data set shown.
  • Access to Lists, already filtered by Fabrik, further controlled by the Joomla! security framework.
  • The data set shown in Forms maybe even further controlled by the Joomla! Menu filters.
  • Useful data is duplicated in the “dark” database with greater design discipline.

Address Database

To illustrate this just consider an Address Database. The first and only required element is a primary key - to make records unique. Then, the database is expanded…

  • Mailing address
  • Web and Online addresses
  • Floor Space
  • Shipping docks
  • Open for Public Hours
  • Lease Costs
  • Audit Information
  • etc…

This could be a factory outlet address on “123 Industrial Drive, BIGCITY ST 00000-0000” with web address “http”, 34,000 square foot, 16 Shipping Docks, Open to the Public every Sat 9 AM to 5 PM, costing 12345.00 USD per month, etc.. etc..

All this and more is stored in a single table. Fabrik DOES NOT normalize SQL data, and FourOh-LLC does not care about normalizing or any other way optimizing databases which are used for pilots. The purpose of pilots is to refine purpose, sequence processes, validate functionality, not to tune performance.

The Joomla! Menu

When Goldy logs into Joomla! she is presented with a set of drop-down menus. Since she is the owner of Goldilocks she is going to see more menu items than her employees - and she is likely to see for RENT, for LEASE or for SALE lists, and those already on her books.

There are many, many more menus - but they are not visible to her. Furthermore, she might see them but she cannot update or delete them. The fact is, the Joomla! Menu may further filter the list - so she might not see what the SELLER sees.

The Fabrik Lists

Each menu item holds a list of already filtered resources, and your Joomla! login determines which of these are visible to you. However, not every detail is available to you. For example, you might not see all FIELDS - you might see the phone number of the business front desk, but not the phone number of the building custodian.

You might not see all records: the building might have 10 floors, and you only see the first - the other nine is not relevant to you, and shown to you.

You are not going to see the record change history, the records related to government or industry regulations. Auditory data might only be visible to the technology administrators.

You might not be able to access the Update form - all you might see is a list with a few details.

The Fabrik Forms

When you are able to access details you are presented with the Add New Records form. The Update form is the same, except the form is loaded with data from the database.

Fabrik has the capacity to present sub-forms - in the many cases where you have a list attached: “color” has the “red”, “blue”, “yellow” items.

There are countless other options and choices - and you need to master the “best practices” when building these forms. For example, where you should use radio buttons in place of drop-down lists, and if you should use the radio buttons to sequence steps - like “called for quote”, “quote received”, “quote accepted”.

The forms are very easy to construct and use - but you need to make sure you need every one of them, and each field on each form has a purpose.

The Data

While the visible 'lighten up' database is populated ad-hoc in impulse-based fashion - there is a process on the database back-end. You may attach scripts to every CRUD (Create, Read, Update, Delete) operation - and those scripts going to copy some data from the mess that is the Fabrik database into a separate, invisible 'darkened' database!

The dark database is carefully designed, fully normalized and optimized, and ready to be audited by enterprise service providers, for migration.

This is why the FourOh-LLC pilots need to be brief, quick, simple - so the requirements to create the dark database is realized, learned, refined and documented.

Furthermore, not every situation lends itself to this approach - to realize if this is a viable solution the Pilots must be well focused - with clear objectives about where to start, and when to conclude.

The Light Database

Data for the lighten-up database may come from any place, in nearly any shape and form. Key entry, barcode scans, excel uploads, script-generated sequences - whatever source you have available. Fabrik allows you to create quick and dirty web forms, you may use phpMyAdmin, or even shell transfer scripts.

You do not need to be overly concerned with that data, and you may pre-process or post-process in batches. For example when you have a phone-list in Excel - just create the table in phpMyAdmin and upload the spreadsheet. Then, import the table into Fabrik as a List, and begin your pilot by renaming the fields, changing the data type, adding, merging, deleting as you see fit.

The Dark Database

Data is transferred to this database by triggers during operation, or by routines by scheduled maintenance. This allows testing and certifying the transfer scripts, ensuring reliability and data integrity.

However: the person doing this should be the master of your:

  • Industry (manufacturing, warehouse)
  • SQL (MySQL / MariaDB / Percona)
  • Your Target Platform (SAP, Dolibarr, xTuple)

among other things. The Dark Database should be built slowly and based on lessons you learn from the relevant FourOh-LLC Pilots. Many times this person or group is going to be the Application Architect, the Database Architect, or related professionals from the Target Platform.

In theory you could conclude a FourOh-LLC pilot with a database ready to be migrated to the Target Platform - but this is not likely. The dark database is needed as a translation layer between the messy experimentation on FourOh-LLC, and the principled and mission-critical Target Platform.

Functionality

Fabrik allows attaching php scripts, and making the Forms and Lists more functional. However - you need to keep in mind that your purpose here SHOULD be PILOTING: to build the raw database and basic functionality, to validate a business logic, to demonstrate a candidate solution. The FourOh-LLC Software Stack is not a feature-loaded, polished enterprise platform.

While the 'light' database is the one you build and populate (with or without the help of FourOh-LLC) - the dark 'database' requires expert help, preferably a Data Architect specializing on your target platform. FourOh-LLC cannot help you with this since those target platforms are often closed-source, closely guarded business assets. They are also quite large and fairly complex - nothing like the minimalist and simplified FourOh-LLC Software Stack.

If your Target Platform is fully closed - you are out of luck. You are not going to make much use of FourOh-LLC Pilots, as migrating your Pilot to the Production platforms is not going to happen. Hope to see you some other time.

If you have no target platform - you are in the best position! You may build all sorts of pilots, and a Data Architect may migrate your pilots to Open Source target platforms without any difficulties. This is the best situation, as you may select your future, fully compatible Production Platform based on a verified FourOh-LLC Pilot.

Backup and Restore

Joomla! has an excellent extension called Akeeba Backup - I guarantee that you are going to need it. Fabrik takes some time to master, and during that time you are going to lose access to your Lists and Forms due to mistakes. The most common mistake is moving or deleting the Primary Key.

When you poke the Joomla! security system you might lose access to your entire installation - Akeeba is not going to help you there. So be prepared to restore the VPS from backup, which is easy enough to do - but you might destroy data in other parts of the system such as Wiki content or email.

It helps to test and learn this early on - so you know when to backup, which restore to use, and avoid data loss.

the_database/relational_sql.txt · Last modified: 2020/02/15 09:26 by admin2welcome