Important to split an Access database into > 1 table? necessary?

[Guate]

How important is it to split an Access database into > 1 table? Is it
necessary?

 

[Jeff Boyce]

Common usage of the term "split an Access database" refers to placing all
tables in one .mdb file (the "back-end"), and putting all other objects in
another .mdb file (the "front-end").

Deciding how many tables to use depends on your domain of interest, and the
degree of normalization you decide to apply.

Regards

Jeff Boyce
<Access MVP>

 

[Guate]

Thanks, Jeff. This particular database will not be used in a multi-user
environment, so my use of the term "splitting" the database (into a front end
and back end) probably isn't correct. I was thinking more in terms of using
the Table Analyzer to "divide" the database into a couple of tables rather
than a single one, as it is currently structured.

If a database is small--say, < 10,000 records--is it OK to store all the
data in a single table? I keep reading that it's advisable to place related
data in separate (related) tables, but is it necessary?

This is a simple volunteer database for a small NGO--with contact info,
skill areas, hours worked, etc. Can all this go into a single table (i.e. 1
record per volunteer) or should each of these sections constitute a separate,
related table? What are the advantages/disadvantages of using the Table
Analyzer? (A number of queries have already been constructed.)

Thanks, Jeff; I appreciate your advice!

Guate

 

[John Vinson]

How important is it to split an Access database into > 1 table? Is it
necessary?

"Is it important to use expressions in spreadsheet cells?"

An Access database should have as few tables as are necessary - but no
fewer. The art and science of database normalization is a deep and
complex one, about which many books and doctoral theses have been
written.

The short lesson is - "Identify the Entities". An Entity is a person,
thing or event of importance to your application. Each distinct type
of Entity should have its own table. If you find that you're storing
repeating data in a table - such as having fields for January,
February, March or fields for Answer1, Answer2 and Answer3 - then you
need another table, because you have a one to many relationship
between this table and a MonthData entity or an Answer entity,
respectively.

As a rule, though, a database with only one table would be a very
unusual one; you would hardly need to use the complexity of Access in
such a case, as you could store the same information in an Excel
spreadsheet.

John W. Vinson[MVP]

 

[Randy Wayne]

Jeff referenced "normalization", which is the key to answering your question.

Simply put, normalization involves building your database to a specific
value only once. There are entire books written on the subject but I will
give you a very basic example.

Imagine a baseball league with 4 teams and ten players per team. You could
build a a one table database with the following fields:

Player Name
Team Name

Simple yes, but you would need to add the team name to each player. It is
not just an issue of extra typing - but suppose you mispelled the team name
"Reds" as "Red" for one player. If you were to need to build a query to
search for all members of the Team "Reds", you would miss the player in team
"Red".

Now, imagine that the "Reds" change their name to "Blues". You would need
to change each record. Sure, this could be done with an Update query, but it
would still miss the mispelled player with team "Red".

A normalized database would have a table of Players and a table of Teams.
In the Players table would be the following fields:

Player_ID
PlayerName
Team_ID (foreign key) FK

The Team table would have the following following fields:

Team_ID (primary key) PK
TeamName

Join your tables on the Team_ID as a one to many.

Now, if you need to change the team name, you change it once, in the Teams
table, because of the relationship it will be changed for each player on the
team. Also, you only have to type it once.

This is the basis of a relational database - otherwise, you simply have a
spreadsheet. As your database grow in complexity, normalization becomes more
and more important.

Finally, to continue to build a better database, you would split the
PlayerName field into PlayerFirstName and PlayerSecondName. Then you could
sort of the last name, something you could not do with the original
PlayerName field.

The opportunites go on and on, but this should show you what Jeff was
referring to.

Let me know if this helps,

Randy

 

[Guate]

Randy, thanks very much--this is a very clear, simple explanation! I
appreciate the clarity!

Just out of curiosity, would a Look-Up list not resolve the first issue you
raised (miss-keying Reds as Red)? I believe I read somewhere that Look-up
lists are not a good idea, but I don't recall the reason why. What is your
view?

Thanks again, Randy!
Guate

 

[Guate]

Thanks, John; another very helpful response--what would we novices do without
you guys?! Your MonthData example as a one-to-many relationship was a great
example of where I do need to create a separate table within this database in
order to register hours worked by each volunteer in January, February, March,
etc. Thanks again!

 

[Guate]

P.S. John & Randy, is the Table Analyzer an acceptable option for separating
out the monthly "hours worked" fields into a separate table? or would you
suggest a different approach?

Thanks again...

 

[tina]

Just out of curiosity, would a Look-Up list not resolve the first issue
you

raised (miss-keying Reds as Red)? I believe I read somewhere that Look-up
lists are not a good idea, but I don't recall the reason why.

no, you really don't want to use any Lookup fields in your tables - most
definitely not a good idea. for a number of good reasons why not, see
http://www.mvps.org/access/lookupfields.htm

data entry should be performed in forms anyway - not in tables, so a Lookup
field serves no purpose in a table. combo boxes and listboxes, created in
forms, provide the same "ease of use" for data entry - without any of the
drawbacks and problems caused by Lookup fields at the table level.

hth

 

[tina]

This is a simple volunteer database for a small NGO--with contact info,

skill areas, hours worked, etc.

from just this brief description, i'm thinking you have the potential for
two or three "child" tables related to your Volunteers table, at least. for
instance:
1) could you have more than one point of contact for a volunteer? i'm
thinking specifically of multiple phone numbers, such as home phone, cell
phone, pager, etc.
2) it's likely that a volunteer could have more than one skill to offer the
organization.
3) and of course, the hours worked - which you already identified.

an analysis of the data you want to track might reveal more one-to-many
relationships that need to be expressed with parent-child tables. you're in
the best position to make that analysis. suggest you read up on data
modeling / normalization; once you learn the basics, you can apply the rules
to analyzing your own database - and build the tables/relationships
correctly. one good text is Database Design for Mere Mortals by Michael
Hernandez. also, see the links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Database Design 101 and Starting Out links.

hth

 

[Guate]

Thanks, Tina. Yeah, the data are all being entered via a form, with combo
boxes, etc. Still fumbling a bit with the terminology....

Your recommendation that lookup fields not be used means you would not
encourage the use of the Table Analyzer, to divide a large table into
smaller, related tables, right?

Thanks again for your help!

 

[tina]

i've never used the table analyzer; i'm in a better position to analyze my
business process than any wizard, so i do the work myself. if the table
analyzer creates Lookup fields, then i would definitely not recommend using
it. see my comments re tables/relationships elsewhere in this thread.

hth

 

[John Vinson]

P.S. John & Randy, is the Table Analyzer an acceptable option for separating
out the monthly "hours worked" fields into a separate table? or would you
suggest a different approach?

Well... as Tina says, no automated wizard can possibly understand your
data AT ALL (artificial intelligence is a long way off, but artificial
stupidity is alive and well <g>). The analyzer can sometimes give some
useful suggestions, but - in my opinion - JUST suggestions; you should
not assume that it is doing a complete or accurate job, since so much
of the art of normalization depends on a human, intellectual
understanding of the nature of the data.

So I'd say - you can use it; just don't rely on its suggestions as
being authoritative. You may be better off to follow Tina's advice,
and manually identify your Entities and Relationships.


John W. Vinson[MVP]