One Primary Table

[JD McLeod]

I have a database project with one primary table in it. I have several
supplemental tables, but one main table. It is a very large table with a lot
of data in it, but it seems to work ok.
Question>> As a general rule, is it better to have fewer tables if you can
get by that way?

Also, some of the supplemental tables are included to make data entry on a
form easier. For example, one piece of data the user must enter in the
database is the type of audit procedure they are performing. My data entry
form uses a drop-down box for this. All I care about is whether it is
preventive (P) or detective (D). The (P) or (D) is what is actually stored
in the database, but the form has to display the text preventive or detective
along with the letter so that the auditors won’t forget what they stand for.
That is why I have the second table. I am able to have two columns and it
makes the form work easier.
Question>> Is there a way I can accomplish this without having to have these
supplemental type tables?

Thanks. I am updating a project that was used last year and it seemed to
work fine, but I just wanted to streamline it this year.

 

[Jeff Boyce]

Relational database design (normalization) is not about the number of
tables. To make the best use of the features and functions Access offers,
consider reading up on the notions re: normalization.

When you say you have "a very large table", does that mean you have more
than 30 columns/fields? If so, you might end up with "cleaner" data and
make better use of Access if you normalize the data. I'm not saying a
well-normalized table could NEVER have more than 30 fields, but it would be
very rare.

Your "supplemental" tables sound like classic lookup tables. This is a very
common and useful approach to ensuring that data entry is restricted to
valid values.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[JD McLeod]

Thanks Jeff,
I do have around 30 columns in my table. The table stores all of the
information for business process controls and the results of the quarterly
testing on those process controls. Maybe I should have a separate table for
the testing results, using the control reference number as the link between
the two tables? Does that sound like a good idea? I am somewhat new to
access and still trying to learn. Thanks.

 

[mnature]

Try looking here for tips on normalization:

http://support.microsoft.com/kb/283878/EN-US/

Basically . . .

1) Don't repeat groups in individual tables, such as using fields with the
names vendor1, vendor2, and vendor3.
2) Put all related data into their own tables. Customers place orders, but
customers and orders should each have separate tables.
3) Tables should only contain data that is related to each other in some way.
4) Use primary keys to identify the related data that is in its own table.
Each record in a table should contain specific data that is related through a
unique primary key.

Now, consider how each table relates to another. In a simple relationship

5) Use foreign keys to relate a record in one table with a record in
another table. If you have an orders table, and a customers table, you want
to show which customer has placed an order, so you include a foreign key in
the orders table, which will contain the same number as the primary key (from
the customers table) of the customer placing the order. Generally speaking,
you use the same name for the foreign key as you used for the primary key.

Once you have done those, then

6) Create separate tables for values that apply to multiple records. This
will be used in instances such as when you use a category to identify a
product. There can be many products that fall under the same category. You
can put all categories into a table, and then use a foreign key to signify
which category applies to a product.

In addition,

7) When there are values in two tables that relate many-to-many, you will
need to create a linking table between those two tables. This will be used
in instances such as when you have personnel who have training. The linking
table will have its own unique primary key, and will use a foreign key that
relates to a person, and a foreign key that relates to a training class.
This will enable you to have numerous classes for each person, or numerous
people for each class.

 

[Pat Hartman\(MVP\)]

Since quarterly results will be recorded 4 times a year, your present design
would require duplication of the business process data. Since you have a 1
(business process) to many (quarterly testing) relationship, you need two
tables for this data.