WRITER’S GUILD OF ALBERTA

Database Optimization

OVERVIEW

KEY CONCERNS

Pertinent information split across two database: in-house Access and online MySQL

Current database tables are non-relational leading to unnecessary duplication and inefficiencies

Unable to produce custom queries or generate custom reports

Unable to produce receipts for online transactions

POSSIBLE SOLUTIONS

Add to Access Manually import

MySQL data into Access

Purchase software licenses for programs to add necessary features

Use MySQL Move Access data

into MySQL Custom-build user

interface to MySQL or learn phpMyAdmin

Write PHP code to add necessary features

POSSIBLE SOLUTIONS

Middle Way Migrate database to MySQL Use Access/FilemakerPro as Front-end

interface Develop PHP app for additional features

as needed

Scope of Plan

Database Redesign Coordinating with Webhost Data Migration Developing a User Interface Additional Features Database Administrators Responsibilities Estimated Budget

DATABASE REDESIGN

Access Database on in-house server

209 entities across 12 tables

Access Database

Stores registration and payment information

Emails notify WGA of new members or payments

Blue Genesis

Analyzing Redundancy

Contact information spread across several tables and on both Access and MySQL databases

Payment information stored on Access and MySQL for member renewals

Registration fees for events separated from other payments

209 entities on Access reduced to 102 entities using relational data

Redesigned database relations

Database Organization

Contacts for all members, youth members, parents, donor organizations, venues, publishers, authors, judges in one table

All payment receipts in one table Date stamps generated

automatically Junction tables connect contacts to

events, awards, retreats, etc. Member/donation categories stored

in separate tables to normalize data

Database Software

Selected software based on criteria Relational database Ease of connecting to Blue Genesis

server Ability to generating custom reports Cost for software license Expertise required to manage software

Access

Relational Database Can create SQL queries

and Custom Reports Can connect to MySQL User familiarity with

interface Designed for users with

a range of expertise

PROS CONS

Some desired functionality requires 3rd party software

No email generation, uses other Office apps

Still requires complete database redesign

Requires training building SQL queries and custom reports

Oracle

Relational database

Enterprise support

Can create custom queries and reports

Feature-rich

PROS CONS

Enterprise license is $7000

Requires training Too complex for

our purpose

MySQL

Some training needed to script queries and reports

Data migration will require more steps

Additional features require PHP scripting

Will likely need to hire external help

PROS CONS

Open source and free Relational database Custom reports and

queries Strong community for

support and apps Installed on current

webhost’s server Will move data online

Recommendations

Begin redesigning database using table relations as soon as possible

Use junction tables to connect people to events, awards, programs, renewals

Rely on SQL queries to pull specific information from tables

Choose to redesign tables in Access for short-term solution or migrate date to MySQL for long-term solution

Store data online if possible; risk of losing data because in-house server failure

COORDINATING WITH WEBHOST

Blue Genesis

MySQL and PHP supported Currently hosts data from website Current package permits hosting

additional information Suggests using phpMyAdmin but will

support Navicat (Digital Tea’s preferred software)

Will require assistance from Tech Support to establish connection to hosted database

Digital Tea

Willing to build administrator website to manage data on MySQL server if required

Can add onto administrator functionality currently on website to manage member payments

Considerations

There are webhosts that are less expensive than Blue Genesis that have MySQL and PHP installed on their servers

Blue Genesis can host database and provide minimal support at no additional cost

Digital Tea can add user interface onto current website infrastructure: may be expensive but will also provide long-term support

DATA MIGRATION

Preparing Data

Back up current database in new file Export data in .csv or .xlsx format Edit the information in Excel to match

format of new tables

Preparing Data

Create new Access file

Import .csv file Assign foreign key

relationships in Access

Upload new .csv file to Data Migration Tool (DMT) such as MySQL Workbench

Create foreign key relationships in DMT

DMT will generate the scripts to create MySQL tables and insert data

Access MySQL

MySQL Workbench

Edit table data

Organize data relations

Establish connection to server

Automatically generate SQL scripts to create tables and keys

Recommendations

Have a solid data model before beginning migration and organizing new tables. More difficult to make large changes after migration.

Data loss and unexpected formatting changes most likely to occur in this step. Back-up often.

Work closely with Blue Genesis Tech Support to set-up mirror tables before making changes on MySQL server to prevent overwriting current data.

USER INTERFACE

Available Software

Free database administration software is widely available for MySQL

Typically for dedicated DB administrators

None of these options can automatically generate pdfs or emails

Excellent free open-source software with dedicated community

Interface is fairly intuitive after learning how MySQL is structured

phpMyAdmin

Cost: $69

Includes report and form generation software

DBTools Manager

Cost: $99 + $39/yearly renewal

Well designed with many features

dbForge Studio

Custom-built Interface using PHP

Interface options available to novice users limited

PHP-built user interface to administer MySQL through WGA website a possibility

Would allow WGA to incorporate custom features like PDF generation and mailing directly in database administration interface using PHP

Most expensive upfront in terms of time & money

Another Alternative

MySQL ODBC Connector

MySQL can connect with either Access or FilemakerPro with the ODBC Connector

FilemakerPro 12 also contains PDF generator and mass mail feature

Could migrate data to MySQL and use ODBC connector to manage data through Access

Unsure if it will allow you to add additional feautres: more confident in PHP scripting

Connecting MySQL to Access

Possible Short-term solution to connect to Blue Genesis

MySQL ODBC connector allows you to read info in MySQL from Access.

Recommendations

Try to use MySQL ODBC connector as temporary solution

Assess possibility of using Access/Filemaker as user interface

Consider whether training DB admin in SQL is feasible for WGA

ADDITIONAL FEATURES

Custom reports

Custom reports are handled in all software presented but requires knowledge in creating SQL queries

PHP-built interface would likely contain a few report templates that would allow you to pull results based on date or member-status

Available software

PHP Report Maker

$159.99 + $79.99 upgrade

Generates custom reports from MySQL using PHP

PDF Generation

PHP has its own PDF function library FPDF and TCPDF free extensions to PHP

that generate PDFs Difficult to find database software that

will generate PDFs automatically Alternative may be to generate receipt

for donations/renewals as webpages that the client can print out

Email and Mass Mailer

Email scripts not difficult to make in PHP Difficult to find DB Admin software with

feature May be possible to build small email PHP

app to send emails after migration to MySQL

Email Software

Total Access Emailer a 3rd party app for Access

$199-$299 depending on version

Eventbrite

Coordinating data with Eventbrite should be little trouble as long as data can be exported to .csv or .xslx file.

Considerations

Custom reports easiest to create in dedicated DB Admin software

Even limited to a few templates, custom reporting difficult to build from the ground-up in PHP

Email and PDF creation easier to create in PHP

Software solutions available to integrate with Access

DATABASE ADMINISTRATOR

Training

To use the relational database minimal training in SQL, query creation, and custom forms will be required even if WGA uses Access

Data migration will require knowledge in creating MySQL tables and using MySQL Workbench Additional SQL training may be required if WGA uses phpMyAdmin to manage data

Help from Blue Genesis tech support likely needed to establish MySQL connection

Recommendation

It is possible to roll responsibilities into current member manager’s duties

Unlikely that new member manager will know SQL and will likely need on-job training

Best solution is to have SQL scripts running under the hood of a PHP-powered website or create report templates in Access/FilemakerPro

ESTIMATED BUDGET

Time

Depends on the amount of data in current tables

60-80 hours to create new tables and export, format, upload information from old tables

Hard to estimate time to build PHP user-interface: possibly 200 hours+

Software Cost

Purchasing software to augment Access Office Professional $669.00 FilemakerPro $299.00 dbForge $99.00 DBTools $69.00 PHP Report Maker $240.00 Total Email Access $299.00

Most expensive combination = ~$1300 Building PHP interface (200 hrs * $30/hr)

= $6000

RECOMMENDATIONS

Reorganize Database

Although labour intensive, database optimization can be completed in-house with the help of CSL/Huco.

Test out the MySQL ODBC Connector to see if it will satisfactorily connect to MySQL database

Start to learn the basics of SQL within Access. You can build custom reports in Access using queries

User Interface

Consider how knowledgeable you expect the DB admin to become before deciding which interface to use

If you have a student build a PHP website consider how you will maintain this website after the student leaves

If you have Digital Tea build website, weight long-term support into the cost

Best solution may be to use Access or FilemakerPro to connect to MySQL database

Features

Despite the licensing cost, buying 3rd party apps may be cheaper than building custom software

Trial 3rd party apps: might be too much for your needs and might slow down system

Consider asking a CSL/Huco student to build smaller PHP apps rather than whole website, i.e. mass emailer app or PDF receipt generator

THANK YOU

Questions?