Microsoft Access Small Business Solutions
April 9, 2010
Microsoft Access Small Business Solutions, published by Wiley, proves that Microsoft Access can be used to create valuable business solutions in theory and in practice by providing both the reasoning for the database design and the databases themselves in the CD that accompanies the book.
So much of our information comes from online sources these days, but I still like books and Im always on the lookout for a new technical read. For Access developers, there have always been plenty to choose from and with Office 2010 coming out, there will soon be even more. One new title recently caught my eye: Microsoft Access Small Business Solutions published by Wiley. Its not about the wiz-bang features of Access 2010 but rather a back-to-basics book written by a team of what I consider to be the most talented Access programmers known to the community.
A few months ago I wrote about why Access still rocks for developers and I took a lot of criticism for it. Some people just dont like Access. But I also heard from many developers who have proved, and continue to prove, that Microsoft Access can be used to create valuable business solutions, especially for small businesses with correspondingly modest IT budgets. This new book proves the precept in theory and in practice by providing both the reasoning for the database design and the databases themselves in the CD that accompanies the book.
First off, let me mention that the subtitle of the book is State-of-the-Art Database Models for Sales, Marketing, Customer Management, and More Key Business Activities. This is an important distinction because the solutions discussed are database solutions, not application solutions. While the authors include some queries, forms and code, the emphasis is primarily on building a good database foundation based on the models they prescribe.
Accordingly, the book begins with the basics, explaining Tables, Fields, Data Types and Indexes. If you are familiar with databases this will be boring, but a book like this could hardly neglect the topic. Next it discusses the Relational Data Model concepts proposed by Edgar Codd in 1969. Normalization is described in detail, as it should be in a book of this nature and while Ive read it all before many times, I must admit the refresher course was helpful. In fact, I learned something new: The relational in Relational Data Model refers, not to relationships between tables, but to the tables themselves. According to the authors, the term relation was the original designation for what we call a table.
Starting with chapter 4 the book reveals a series of parts:
Part II Dealing With Customers and Customer Data
Chapters 4 through 15 describe how to model a database on the topic under consideration and the finished product MDB file(s) that correspond to each chapter are available on the resource CD. The same is true for topics in several of the appendixes. This sort of tangible, hands-on code makes the book a real value. I hate to admit it but Im basically lazy and I dont like to type too much so when I find a solution, I prefer to copy and paste. Some of the resources in this book which may be leveraged in this way include:
table sets for various business objects
What Was Done Well
The most common criticism against Microsoft Access Solutions is that its too easy for people with limited experience to build bad databases. While that may be true, the solution is hardly to ban Access entirely. We believe this issue can be addressed with education and examples. Thats what this book does best.
Below are a few of the questions addressed in the book:
1) Should I use an AutoNumber as the primary key?
2) How should I name my tables? ID fields?
3) How might one handle deleted data? Audit trails?
4) What is a relationship triangle and how should it be handled?
5) When does it make sense to denormalize the data?
The complexity gradually increases with each chapter as the authors explain their reasons for constructing the solutions/models as they have. Someone new to database design would do well to read the book all the way through, even if the solution they seek is introduced in an early chapter. Some things implemented early are not fully explained until later.
What Could Have Been Better
I believe the word model should have replaced the word solution in the books title. Maybe its just me, but when I think of solutions, I think of final products. While some of the sample MDB files include forms that implement the database models, UI design is not the primary focus. It is the tables, their structure, their relationships and the process of arriving at each that is the thrust of this book.
This is, of course, an excellent objective but it was not obvious to me until I delved into the book a little ways. The forms that are included are not pretty and its not always easy to understand what they are attempting to demonstrate. They contain no error handling and for the most part are not among the things that may be copy-pasted into your applications. In all fairness, this is also true of sample code I often supply with articles though my demos would never be confused with solutions.
If you keep in mind that the purpose of this instruction is to help you build a good foundation by modeling your database after one that is solid and easy to maintain and expand, then you will not be disappointed. At least thats true for people new to database design. Old timers who are set in their ways may take issue with some of the conventions proposed.
The newsgroups are filled with heated debates about the advantages of ADPs over MDBs, whether an SSN can serve as a primary key and proper standards for naming conventions. The authors represent a point-of-view that is very solid, one you cannot go wrong following, but one that lends itself to disagreement.
One example I found was the assignment of the size property for text fields. In their sample databases, most field sizes defaulted to 50 characters, unless another value was obvious, like 2 characters for a state code. I personally advocate using a larger variety of field sizes, following either Binary or Fibonacci numbers. (2, 4, 8, 16, 32 etc. or 1, 2, 3, 5, 8, 13, 21 etc.) Its a personal preference but one that would have impressed me had the authors had included it.
In other cases there were simply mistakes in assigning types to fields. In the People database the [LastUpdatedBy] text field was set to 255 characters, the maximum size allowed. I cant imagine it needs to be that big. In the Accounting database the [CreatedBy] field had the same data type as the [CreatedDate] field, namely a Date/Time type. This appears to simply be wrong. These are mistakes that should have been caught. Perhaps an updated download will be available at the Wiley web site after such issues are found and corrected.
As Luke Chung said in the forward to the book, The strength of Microsoft Access is that with the proper mastery of its features, you can create database applications that meet the needs of your organization quickly and cost effectively. Building on a strong foundation is an important first step and the models described in this book will help.
Ive known and consulted with some of these authors for over a decade on the Access newsgroups and I have great respect for their expertise. I have no doubt that the many pro-bono hours they contributed to newsgroups helped them to prepare this volume. I was one of those who benefited from the online discussions years ago. In my opinion their book, Microsoft Access Small Business Solutions, boils down some of the best practices into a single, handy reference.