Sample Table Design Structure (1 Viewer)

Status
Not open for further replies.

ajetrumpet

Banned
Local time
Today, 15:24
Joined
Jun 22, 2007
Messages
5,638
I am writing this random FAQ to share some of the knowledge I have gained by setting up efficient database systems. I have seen a lot of design structures that are (quite honestly) unnecessarily complicated. Here is my 2 cents worth (or maybe a bit more) on this subject:


I am not the greatest expert in the world on MS Access, but most of the situations that I have run into can be coupled with a database that uses the very popular one-to-many property for table relationships. And, come to think of it, I know of only very few instances where this type of relationship will not satisfy the needs of the tables in an entire database. I regret saying this, but many of the people here on the forum that give good advice say this phrase quite often: "You need to normalize your data". I have had discussions with other people that think normalization is not necessary (and believe me, sometimes it is not!), but when the advice comes from this board, more than likely the data in question is not set up in the most efficient manner (hence, it needs to be normalized).

Efficiency of a database system is all about the underlying data sources (in this program, it is obviously the data tables). In my opinion, forms, reports, and macros (and any other "display-type" or "data-manipulation" tool) have absolutely nothing to do with the efficiency potential your database can have. Yes, everything (objects, code, etc...) plays a part in efficiency, but sourcing of the data storage is where it all starts. With that said, it is also noteworthy to mention the database engine. Many people don't even realize that this "thing" even exists, but without it, you couldn't even produce a single report for your boss! :)

The purpose of the engine (in my view anyway) is to help you extract your source data out of its storage place, and into other objects such as forms and reports. When you write a query for example (SQL statement), you are asking the engine to do something for you ("fetch" what you originally told it to store - the data). OK...enough of the lecture... :)

I have included below an example of what I think would be a typical Access setup for a small business operation (using a Retail example, as it is the easiest to explain and understand). You will notice that the structure is only comprised of one-to-many table relationships. This is not a coincidence. They are by nature, the easiest setup tool to work with, and in my opinion, it is much easier to manipulate data in your mind (or on paper) to work with this type of relationship rather than trying to "bend" the program, or even use more complicated tools within the program to satisfy your data structuring needs. Attached is the example of what I consider to be an "efficient" data model. It is not perfect, but the underlying idea is there. The structure model is what is important. I chose the tables and the types of financial data that I thought might be relevant. Keep in mind that it was comming from an accountant's brain, so that part of the setup is not really relevant. The structure of the tables and relationships are the important parts.

Any feedback is welcome. As always, I hope this proves to be beneficial to the entire community...
 

Attachments

  • structuring (2000).zip
    32.7 KB · Views: 887
  • structuring (2007).zip
    36.2 KB · Views: 867
  • structuring (2002-03).zip
    35 KB · Views: 619
Last edited:

chergh

blah
Local time
Today, 20:24
Joined
Jun 15, 2004
Messages
1,414
Why have you set a report to autoprint on opening the database??? 2002/3 version
 

ajetrumpet

Banned
Local time
Today, 15:24
Joined
Jun 22, 2007
Messages
5,638
Thank you very much for pointing that out. I thought I had gotten that changed. It is now fixed. All of them should be opening as a simple report! :eek:
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom