General - Relational Database Principles

The first time a person tries to use Microsoft Access, they are likely to miss the whole point of what a database is and how to use it. When they start having trouble making it work for them, they think in terms of what they may be doing wrong in their use of the obvious tools of Access. But often their problem is that they are unfamiliar with the principles that make databases so useful and they have started with a flawed database schema.

This tutorial is intended to explain the underlying principles of relational databases. It is, admittedly, rather theoretical and perhaps even boring. But without an understanding of these principles, a beginner cannot hope to use Access for any really useful purpose. If you are a beginner, I urge you to read through the entire tutorial.

There are several issues that arise time and time again, such as:

. Storing multiple values in a field.
. Storing calculated values in a table.
. Determining primary keys.
. Using Autonumbers.
. How many tables does this database need?
. Why don't my queries work?

Sometimes answers in the forum make it sound like these things are a matter of preference, or "which is the best?" But in most cases, they are not--there are well established rules that determine the one correct answer. It is only with an understanding of the principles of relational database theory that you can determine the answers.

So I hope you will find it worth your time to study this 7-page tutorial. If you think you've found something in it that's incorrect, incomplete or impossible to understand, please feel free to contact me. I have been known to make mistakes!

Hi Don,

I was referred to this post by AOG123 via forums.aspfree[DOT COM/microsoft-access-help-18/access-novice-taking-on-large-project-need-guidance-211981[DOT H T M L]#post582634

I'm definitely excited to look over this and glean as much of your knowledge as possible.

Thanks very much!

Gilbert

Hi Gilbert,

As a retired college instructor who taught database courses for years, I have a natural desire to be of help to people who want to learn, as you obviously do. But as AOG123 correctly framed it, I would advise that you carefully consider how you should spend your time. Unlike presentations and spreadsheets, databases are simply not intuitive. Relational database theory is based on some pretty complex mathematics, and while it's not necessary to understand all the theory behind it in order to build small, simple databases, it is also not practical for a beginner to tackle a complex and important application like you're describing.

The issues can be reduced, I think, to the following:

• What is the best use of your time, to learn a complex technical field in a compressed time frame, or do the highly complex work that you are experienced in?
• How much time can your firm afford to spend developing this? It is not at all uncommon for a project like this to take months to complete.
• What are the risks that your amateur-designed system will fail to perform at a crucial time in your operations?

You've presented a fairly complex data model. I won't even take the time to analyze it, because my first impression is that I would need to spend several hours studying and clarifying what your needs are, and I just don't have the time currently. But my estimate (based on estimating that I do for commercial projects that I occasionally still undertake, as recently as last month) is that I might require several weeks of concentrated work to develop such a database; and that's after nearly 20 years of experience in teaching and consulting. That's my assessment of the task. It's possible that someone else who has done essentially the same kind of project (I have not) could do it far more efficiently.

Anyway, that's my advice, for what it's worth. If you still want to learn about database theory, I encourage you to do so by starting with something that is (1) not quite so complex, and (2) not so important to your firm. I would be glad to try to give you help along the way, as would many of our Forum regulars, and there's also an ocean of material available online, in the way of tutorials.

Good luck.

Don