Asking for help on breaking down table to smaller tables

[Guest]

I’ve been working with this table for some time now that has about 15 or so
fields in it which I know is way too many. For the life of me, I would like
to break this table down into smaller tables & have them all connected/linked
together of course, but I’m unable to figure out how to go about it. Yes, it
has been suggested to me to read all up on “Tables†& “Table Designing†which
I’ve tried, though the dots in my head are still not connecting. If there is
anyone here on this group who might be willing to look at my table (by me
typing out all of the fields & explaining) & giving me some suggestions on
how to break them down. Or, maybe someone knows of a website which offers
this type of assistance? Any input would be appreciated.

 

[Guest]

15 fields is not necessarily a problem! I have tables with more and I have
tables with less. the issue should not be the number of fields but rather do
you have, or will you end up with, repetitive data being store uselessly?

Here are a series of links that cover this subject.

http://www.datamodel.org/NormalizationRules.html
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
http://www.databasejournal.com/sqletc/article.php/1428511
http://www.informit.com/articles/article.asp?p=30646&rl=1

If you past more info on what you database is about and a list of your
fields, we will help you design the table(s) properly.

Daniel

 

[John W. Vinson]

I’ve been working with this table for some time now that has about 15 or so
fields in it which I know is way too many. For the life of me, I would like
to break this table down into smaller tables & have them all connected/linked
together of course, but I’m unable to figure out how to go about it. Yes, it
has been suggested to me to read all up on “Tables” & “Table Designing” which
I’ve tried, though the dots in my head are still not connecting. If there is
anyone here on this group who might be willing to look at my table (by me
typing out all of the fields & explaining) & giving me some suggestions on
how to break them down. Or, maybe someone knows of a website which offers
this type of assistance? Any input would be appreciated.

15 fields is not too many if they are properly normalized,
nonrepeating fields; Access supports up to an absurdly huge 255
fields, and I've used as many as 60 in a table. If each field is
atomic, does not depend on any other fields, and is not part of a
group of repeating fields (fieldnames like Agent1, Agent2 and Agent3
or March, April, May are examples of the latter) you're probably fine.

If you wish to post the fifteen-field list someone should be able to
help (you can use Tools... Analyze... Documenter to create a Report,
and copy and paste just the field definitions if you want to save some
typing); and/or check the references at

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
and
The Access Web resources page:
http://www.mvps.org/access/resources/index.html

The "Database Design 101" links on Jeff's page may be particularly
helpful.

John W. Vinson [MVP]

 

[John W. Vinson]

Hello,

I have a database with the same problem. It contains one primary table

with a whole 121 fields. I know that is way too many. Most of them
could
be broken down into their own seperate tables, which is what I want to
do.
Unfortunately, the Table Analyzer in Access does not accomplish what I
need
because it generates a new Primary key for each new table and limits
each
new table to only 15 fields. Some of the new tables need up to 25
fields,
and each record in the new table will relate to its own unique record
in the
original table, so I want the same primary key for each new table
linked
through the table relationships with referential integrity enabled.


I have looked at the websites provided in the previous posts and
unfortunately what I need is a tutorial on how this normalization
can be done in Access, not a lesson on the concept. Short of copying
the data manually into new tables, I can't think of anything that
wouldn't result in data loss.

A "Normalizing Union Query" will probably get into the mix here at
some point... but first:

Close Access. Select Start... Turn Off Computer... and either Turn Off
or Hibernate. Find a pencil and a pad of paper, and perhaps a good
block eraser.

What are the LOGICAL relationships amongst your data? What Entities -
real-life persons, things, or events - does your database represent?
Each type of Entity will be represented by its own table. What
Attributes are important for each type of Entity - what unique,
independent, atomic "chunks" of information do you need to know about
an entity in a given class of entities? For example, Employees
represent a class of entities (people); each member of that class has
an EmployeeID, a LastName, FirstName, Department, and a number of
other attributes. Each such attribute gets its own field.

Then... check your lists of attributes carefully. If you find
repeating groups of fields, such as Task1, Task2, Task3, Task4; or
January, February, March, ...; then you have *another entity type* and
need another table.

Once you have a properly normalized set of tables, turn your computer
back on, open Access, and create this set of tables.

Then you'll be able to run a series of Append queries to migrate the
data from yhour spreadsheet into the normalized tables.

For some suggestions on how to do this, check out the references at:
Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
and
The Access Web resources page:
http://www.mvps.org/access/resources/index.html

In addition, if you wish, post an indication of the purpose of your
database, the names of a couple of dozen of your fields, and
descriptions of the data within these fields.

John W. Vinson [MVP]

 

[John W. Vinson]

I created this database to help the personnel administration
department perform its duties more efficiently. First of all,
the only thing it really deals with is personnel. No products,
or sales figures or anything like that. Each record in the
database represents an employee, and the only primary
keys are SSNs.
Ok...

One of the things the database keeps track of is dependant
information. Currently, my primary table is populated with
these fields(there are more fields than this. This is just an
example):

So would this kind of setup warrant a normalization process
to separate the personal fields and dependant fields?

Absolutely.

A dependent IS A PERSON, and entity in his or her own right.

Rather than one FIELD - or group of fields - per dependent, you should
have *another table*, with one RECORD per dependent. This table would
have the employee's SSN as a foreign key to the Employee table. This
would let you put as many dependents as needed in the table, just by
adding more records; give you only one field to search to (say) find
out which employees have children named Betty; many, many advantages.

The
table also has fields for categories like security, vehicles,
training received, issued equipment, etc. For another
example, we have about a dozen or so company owned
vehicles that must be checked out each morning to people
who are authorized to drive them, so the fields in the
database look like:

Vehicle1(yes/no)
Vehicle2(yes/no)
Vehicle3(yes/no)
and so on...

Again: repeating fields are bad news; storing data in fieldnames is
even worse news. What do you do when you buy three more vehicles?
Change the structure of your table, all your forms, all your queries,
all your reports that involve vehicles? Ouch!

Instead, you need THREE tables:

Employees
SSN <primary key>
LastName
FirstName
<other biographical data>

Vehicles
VIN <primary key>
Make
Model
<other fields about the vehicle>

VehicleCheckout
SSN <link to Employees, who checked it out>
VIN <link to Vehicles, what jalopy the got stuck with>
DateOut Date/Time <when they checked it out>
DateIn Date/Time <when they returned it>

I imagine most of the other fields are similar: issued equipment is
clearly a one-to-many; likewise training; etc.

You mentioned append queries to acheive this normalization.
I have looked at the links you provided and am still perusing
through, but so far I have again found nothing but lessons
on the CONCEPT of normalization, not step-by-step how-to
instructions for Access. I already know what fields need their
own tables, I just need a way to separate the table without
having to manually reenter everything.

I can't see your actual table, and you may not have the information
you need to do this totally: if the Employee record just has a yes/no
field for Vehicle3, it appears that you're not keeping any record of
WHEN the vehicle was checked out, just that it's out right at the
moment...? In that case, historical data simply doesn't exist so you
*can't* transfer it.

You didn't post information about other areas, but if you would care
to do so, I could suggest how to write a query to migrate the data.
The exact query will depend on your current and new table structure,
it's hard to write an example that won't just be confusing!

John W. Vinson [MVP]

 

[John W. Vinson]

This database
only tracks who has permission to check out each vehicle, which
is the yes/no field.

Ok... in that case you still need two tables in addition to the
Employees table:

Vehicles
VIN <or your vehicle number, something to uniquely identify it>
<information about the vehicle>

VehiclesAllowed
SSN <link to Employees>
VIN <link to Vehicles>

Simply inserting a record (using a combo box on a subform) into
VehiclesAllowed will let you display a list of the vehicles; you can
easily add new vehicles to the Vehicles table, without needing to
change any of your database structures.

John W. Vinson [MVP]