Paul Strauss

Information Management Applications

MS AccessMS ExcelWeb SitesMySQL

What Is MySQL

MySQL is one of several Databases available. A Database is a simply an organized collection of data. (I have described that organization here.) MySQL allows for the creation and maintenance of Tables and Indexes. The Indexes are optional and only allow for the efficient retrieval of data; they are not part of the organization of the data. MySQL supports the insertion, retrieval, updating, and deleting of data using a standard language called SQL. Sometimes programs such as MySQL are referred to as a "database engine". In particular, the creating of forms to enter and edit data or reports to retrieve the data are not part of MySQL. These items are part of the MS Access Database Management System.
This is how it works. Your Tables are designed to reflect your actual data: the items of information that you need to collect and the relationships between them. For example, one Table may contain data on your employees, such as their Name and Address. Another Table would contain their Payroll records. The relationship that MySQL allows you to create and enforce is that all Payroll records need to be belong to exactly one Employee and to specify exactly which Employee owns them. It can be thought of as a Parent-Child relationship. SQL is used to create both Tables and the relationship between them in the database. It is important to understand that not only does MySQL maintain the data in a structured manner, it enforces that structure and the relationships between your data. This makes it impossible to have a Payroll record that does not belong to exactly one Employee because MySQL just won't allow you to insert a Payroll record without somehow indicating which Employee owns it. Similarly, if you delete an Employee, it automatically deletes all of that Employee's Payroll records.
In summary, MySQL is a Database. A Database allows you to store and retrieve your data. MySQL is simply a data repository — it is not concerned with the creation, collecting, editing, or the presentation of the data. MS Access sends MySQL the data it is to store, collected on a Form, and retrieves your data into a Report. MS Access communicates with MySQL via the language SQL in order to send MySQL data and to request data back from MySQL.
Since MS Access comes with its own database engine, called the Jet Database Engine (Jet), why would one use MySQL? Jet is designed for smaller applications with fewer than about 12 simultaneous users. It is also not designed for really large amounts of data. A large amount of data for a Jet Table would be over about 50,000 rows. It becomes increasing more susceptible to corruption when there are more than 12 simultaneous users. That being said, there are still many situations in which it is a completely safe and appropriate database engine. Indeed, I use it myself for the databases I create for my own purposes. It does work well in many situations and it is easy to use. MySQL is indicated when any of these may be true:
MySQL is Open Source software. This means that there is a least one version of it that is free for you to use. You can download a copy of it and simply run it on your computer or on a server or local network in your organization. It is easy to administer and so this is a practical way to employ it. However, if you don't have a server in your organization or if you don't want to be bothered with the minimal admistration tasks, you can rent a server with MySQL on it for $100 per year or less. MySQL databases are offered, often as a standard feature, from many web site hosts. You simply need to have a domain name, for about $15 per year, rent a web server with MySQL, and use that database just as if it existed on your own computer. It is very easy to connect, over the Internet, to your MySQL database on a web server and it is just about as fast as having the data on your computer.