Do More for Less – Transferring Logic Through Your App

You’re probably doing too much work–you should be taking advantage of your DBMS’s facilities to get more done in less time. Andrew Wrigley discusses how the principles of declarative design, cascading, and overriding let you have Jet, SQL Server do more work for you.

Access developers often plunge in and, if they ignore the chorus of fearful angels, the inevitable result is a Greek tragedy–a database that requires ever increasing amounts of code in order to work effectively. Every project is different, but the basic guidelines are written in stone: Don’t spend time fighting and/or duplicating what Access is designed to do for you. Instead, invest in learning how to get the most out of Access. The secret of good Access database design is to only add or override what you absolutely have to. Anything else is fudge.

Continue reading

Posted in Design and Tables | Tagged | Leave a comment

Normalization and Management Advice

In a second article on the normal forms of a relational database, Peter Vogel discusses forms two through four and offers some advice in dealing with management.

One of the reasons I’m taking the time to look at the various normalized forms for databases is to fight a battle I lost a number of years ago. At the time, I was working on a system that had the keen interest of one of the members of upper management. This president had played around with computers to the point where he felt he knew the essentials of good database design. And, to be fair, he did know some of them. However, he didn’t know enough to appreciate the absolute necessity of normalizing data. As I normalized the original database design and the number of tables increased, he would step in and “de-normalize” the design for efficiency’s sake.

Continue reading

Posted in Design and Tables | Tagged | Leave a comment

Working SQL: The Trouble with Normal

In the first of two articles, Peter provides the essential background on normalization. In this article, Peter looks at first normal form and provides a different perspective on primary keys. More importantly, here’s why you should care about first normal form.

Up here in Canada we have a singer-songwriter named Bruce Cockburn. One of his songs has a chorus that goes, “The trouble with normal is/It always gets worse.” Many people feel that way about normalization. The process of normalizing your database isn’t a theoretical issue: Non-normalized databases cost more to build, run, and maintain than normalized databases. And, as I’ll demonstrate later in this article, in non-normalized databases, some activities are practically impossible. Yet it’s an unusual project where someone doesn’t declare that the data should be “de-normalized” for some reason.

Continue reading

Posted in Design and Tables | Tagged | Leave a comment

An Access Project, Part 3: “Relationship” and “Normal”

Dave Irvine returns with his series on Access development. In this article, David steps back to discuss some of the underlying concepts of database design. David applies a system analyst’s approach to data design that you might find more useful than some of the more abstract methodologies currently in use.

Part 1 / Part 2

Thinking back on my college days, I remember when my first course on database theory used a text that began, “To define a database, we must first define reality.” I almost blew that course! However, there’s some truth to the statement. Databases are collections of information used to solve real problems, and are models of real items and objects. Data is used to describe the world around us. A company has employees; the database has an employee table. Employee Dave Irvine gets a raise (I like that!); the salaries table is adjusted to reflect that raise. To understand what a database is, and how to use it as a tool to solve real-life problems, you must first understand what the problem is and the environment, or the world, that the problem lives in.

Continue reading

Posted in Design and Tables | Tagged | Leave a comment

The ABCs of Requirements Management for Access

While Access itself is a development product, developers need to think about much more than writing code. When working with clients, it’s crucial to understand the requirements for the application before writing code. In this article, Mike looks at some of the fundamentals of requirements management for the Access developer.

Let me start with a true story from my own consulting history. I won’t tell you who the client was (my lawyer would frown on that, since he had to get involved in extricating us from the situation), but this was well after I was established in the Access field as a trainer, writer, and developer. Along with a couple of other coders, I signed on to do a major job for a mid-sized (about 100 employees) manufacturing company. What they wanted seemed fairly simple: a bill of materials database, with links back to their accounting system, to let them track the custom work they were doing for various clients of their own. The company’s vice president was pretty savvy about PCs himself and had decided that Access was the language of choice for this project.

Continue reading

Posted in Design and Tables | Leave a comment