Category Archives: Database Design

How to Perform a Basic MySQL Installation in Windows

Leave a reply

One of the nice features of MySQL as a database software is its noinstall option in which you download the files themselves and perform a manual installation that takes just under a gigabyte of space on your drive.  If you’re a developer or a student, this means you can quickly create a new self-contained instance wherever you need it and then blow it away if you need to without cluttering up your registry.

This does mean working on the command line so you’ll want to have a certain comfort level with that.  You will also need to be working with Administrative privileges.

Continue reading →

What is Referential Integrity?

Leave a reply
One of the fundamental differences between an Excel spreadsheet and a Microsoft Access or SQL Server database is the database’s ability to group data by subject into tables and create links between that information.  This type of organization enables the user to store large amounts of data and retrieve specific information quickly by writing queries that will search on the various fields. Referential Integrity is actually a simple but essential concept to understanding how databases like Access or SQL Server work.  Once you understand it, you will be able to use these tools to better organize and report on your data.

To demonstrate this concept, I’m going to use a few tables in a Microsoft Access database.  Access is a great environment to start learning about databases as it provides a user-friendly interface and is widely available through Office365.

Watch the companion video for this article on YouTube or continue article below …

Continue reading →

Quick and Easy Setup of MySQL for Windows

Leave a reply

One of the great things about MySQL is its versatility of installation. It can be setup as a Windows service and trimmed out with some extra utilities or it can be installed on a flash drive as a portable server.

The easiest installation is certainly the Windows Installer with a wizard that will guide you through the installation of the server and all the options you want.  Myself, I still like self-contained programs that won’t interfere with anything else on my system and can be easily removed without leaving hidden traces. I also don’t like adding a lot of stuff to my Windows startup.

If you have a basic comfort with using the Windows command line and control panel, doing a minimal installation of MySQL is not difficult once you understand a few steps. The full documentation is available for reference on MySQL.com but here are the essentials to get you started.

Continue reading →

Calculated Fields in CiviCRM

2 Replies
One of CiviCRM’s strengths is the ability to add custom fields to hold specific information about your contacts. One thing it doesn’t offer (yet) is a calculated field type that will present the results of calculations of other fields.  While calculated fields are generally discouraged in relational database design, they are sometimes necessary within a user interface. One suggested method is to add custom code hooks within CiviCRM’s PHP code but as a database guy, I decided on a back-end solution.

Continue reading →

MySQL User Defined Function Example: Data Cleanup

Leave a reply
(Updated June 9, 2016)

The Challenge

One of my current projects involves migrating a large amount of data away from an old custom Borland Paradox application into a new CiviCRM system. As with too many quickly-constructed apps, this old Paradox database wasn’t especially well designed and, among other the other challenges in salvaging the data, there were no restrictions on how dates could be entered. This means that, in multiple fields within each of the 20,000+ database records, I might see any of the following:

03-14-96
5/20/1975
10/02/83
09051975
07.19.1995
19830211121500

While CiviCRM does have a utility for importing data from CSV and other SQL tables, it was having quite a time with this collection and many of the dates were being mishandled. That’s if the records weren’t rejected entirely for other reasons. Data migration doesn’t often happen with just a few settings adjustments and a click of the Import button.

Continue reading →

Installing MySQL on Mac OS X El Capitan

A few months ago, I wrote about how to install MySQL on Windows with IIS. Now I want to show you how to create an AMP web programming stack (Apache, MySQL & PHP) on Mac OS X. As with Windows, you could just go with a ready-made development environment like MAMP but, if you’re going to be doing real database and web design work with MySQL, it’s best to know how everything works.

Background

For this demonstration, I’m going to be using Mac OS X El Capitan (v.10.11), the latest version as of this writing.  My installation environment is a hosted Mac server with 1 GB of RAM and 40 GB of disk space. I will be demonstrating the process using the Terminal commands.

Unlike Windows, OS X already includes the Apache web server and PHP language pre-installed so it’s a much simpler process to ensure that everything is working together. You will need root access in order to install and configure the components.

Continue reading →

“Microsoft Access has stopped working” – fixing corrupt files

Summary

Occasionally, when you’re making a lot of design changes to a Microsoft Access database, Access might start crashing when you’re working with that specific database file or when trying to enter the VBA environment with ALT-F11. This leaves you unable to investigate the issue or make further changes. It can be a sign that there are errors within the project’s compiled VBA code.  One solution is to use the /decompile switch to remove the compiled VBA code from the project and then recompile the source code.

"Microsoft Access has stopped working" error dialog

Like other Office applications, Microsoft Access can stop working for various reasons. Sometimes, it’s due to a kind of file corruption that’s unique to Access.

Detail

If you’re doing ongoing design work with Access, I recommend creating a new shortcut to the MSACCESS.EXE file on your system with the decompile switch. You would use this only when trying to recover a file. The rest of the time, you should use the normal menu shortcut.

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

You can also enter this with the Run command on the Start menu and include the path to your database file after the /decompile switch.

The first file that you open after running Microsoft Access with this switch will be decompiled. This will not delete the source code behind your forms or reports but it will remove the compiled version of the code and might enable you to get back into the VBA environment.

VBA Compile Database command

The Debug >> Compile Database command compiles all of the VBA code and alerts you to potentially fatal errors.

Once you’re in VBA, use the Debug >> Compile Database command to recompile the database project. This will enable you to find and correct any problems within the code.

As always, be sure to make regular backups of your files as you’re coding in case this doesn’t work and you have to recover. Also be sure to make a backup of the file before you try to decompile it in case something goes wrong.

If this doesn’t work, then the last resort is to rebuild your database by importing all of the objects within it into a new database file. This is time consuming, however, although it does seem to remove a lot of temporary data that Access stores in the file, reducing the database size substantially in some cases.

While you’re here, check out some of the other articles on Microsoft Access right here on ComeauSoftware.com.

Coming in October – MySQL Explained (updated)

Leave a reply

MysqlMy newest book, MySQL Explained, is due to be published this month! You can check out the LeanPub page to show your support and to sign up for notifications of when it’s published.  See the description of the book below.

UPDATE 10/24/2015 – MySQL Explained is now available in both EPUB and MOBI (Kindle) formats with the first chapter available for FREE from LeanPub.com and Amazon.com! Read more about this guide to the leading database for online applications and on the official page!

About the Book

MySQL Explained is a step-by-step tutorial for everyone who’s ready to learn about the database software most commonly used for storing information behind some of today’s most popular websites and online applications.

Written especially for people outside the I.T. world, MySQL Explained provides the background information you need to get familiar with database theory and the principles behind organizing data. This book starts from the ground up, helping the reader to understand the very definition of a database, the forms it can take and the different options for storing information. By the end of this book, you’ll understand the reasons for choosing MySQL, the options for installing it and the tools that it offers to store and safeguard your data.

If you are in any way involved in designing or managing a website or data solution of any kind, you owe it to yourself to understand the tools involved. Quality database management sytems are essential in today’s data-driven world and such essential tools should not be a mystery to those who depend on them. MySQL Explained can help you unravel the mystery and learn more about a technology that will be around for a long time to come.

MySQL Explained is published through OS Training which, in addition to publishing tutorials like this one, offers onsite and online training in web technologies including Joomla, Drupal and WordPress.

Microsoft Access for Beginners – FREE for download

Leave a reply
Just as a reminder, my first book, Microsoft Access for Beginners, is still available as a free download in PDF format from Scribd.com. This book covers versions of Access through 2010 and I’m not planning on updating it further at this time but much of the information is still applicable to the current version of Access. If you’ve been wanting to get an introduction to Microsoft’s desktop database software, this is a great place to start. You’ll learn the principles of organizing data into tables, writing queries on those tables and creating the forms and reports to work with your data as needed. If you’re interested general database principles in addition to Access, please see my other book, Your First Guide to Database Design which is available from both Amazon.com (Kindle edition) and Scribd.com (PDF).

SQL Server for Beginners: Part V – Using SQL Server 2012 LocalDB

In a previous chapter, I described the installation process for SQL Server 2012 Express which included all the bells and whistles of the advanced services package. While it’s great to have all the tools at your disposal to learn from, some people might not want such a large installation on a particular machine or the administration that goes with it but might still want the basic database capabilities of SQL Server on their desktop. In this case, SQL Server 2012 offers a new edition called LocalDB.

LocalDB is a minimized version of SQL Server Express specifically for developers who still need all the programming features including the ability to create stored procedures and other objects within an instance of SQL Server. It has a few restrictions that the average beginner probably won’t be bothered by and you can connect to it with SQL Server Management Studio (SSMS) or other tools to create and manage databases. You can even install the AdventureWorks sample database to work with through an instance of LocalDB.

In this article, I’ll describe the process of installing both LocalDB and SSMS in order to create a small desktop database environment to work with as a developer or a beginner in database design.

Continue reading →