Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Apr 9, 2010

Microsoft Access Small Business Solutions

By Danny Lesandrini

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 I’m 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. It’s 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 don’t 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.

Content Overview

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 I’ve 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
Part III Producing and Tracking the Goods & Services
Part IV Tracking and Analyzing Financial Data
Part V Independent Areas (Memberships, Models & Other Data Sources)

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 I’m basically lazy and I don’t 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:

1)      Related table sets for various business objects
2)      Country and state code tables
3)      Useful Access VBA Code examples
4)      Comprehensive links to additional online resources

What Was Done Well

The most common criticism against Microsoft Access Solutions is that it’s 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. That’s 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 it’s 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 it’s 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 that’s 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.) It’s 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 can’t 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.

Conclusion

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.

I’ve 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.

Additional Resources

Microsoft Access: Customize Forms and Reports
How to Make Charts in Microsoft Access

» See All Articles by Columnist Danny Lesandrini



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date