Problem with normalization

  • Thread starter Thread starter BobC
  • Start date Start date
B

BobC

We will likely be using Access 2007 ... We currently have Access 2000.
I am having a problem thinking though how to set up the tables in order
to get my design going in a reasonable direction. Although the numbers
may change a bit;
We currently have 104 Units
Each Unit has 20 different Models
Each Model has about 7-10 different design factors.

This makes roughly 20,800 different pieces of data that we want to be
able to access. I seem to be going in circles on how to lay out the
tables. I keep thinking of a 3-D array, but I do not think such a thing
is directly available. Could someone please get me started in a
reasonable direction? The idea of having 20 tables does not seem inviting.

Jeff
 
We will likely be using Access 2007 ... We currently have Access 2000.
Click to expand...

Makes absolutely no difference with regard to the logical process of
normalizing your tables; that will require the same logic whether you're using
Access2000, 2007, SQL/Server, MySQL, Oracle, ...
I am having a problem thinking though how to set up the tables in order
to get my design going in a reasonable direction. Although the numbers
may change a bit;
We currently have 104 Units
Each Unit has 20 different Models
Each Model has about 7-10 different design factors.
Click to expand...
This makes roughly 20,800 different pieces of data that we want to be
able to access. I seem to be going in circles on how to lay out the
tables. I keep thinking of a 3-D array, but I do not think such a thing
is directly available. Could someone please get me started in a
reasonable direction? The idea of having 20 tables does not seem inviting.
Click to expand...

Sounds like you have many to many relationships between Units and Models (each
unit may have 0, 1, or more - currently 20 - models; each model may pertain to
0, 1 or more Units; similarly with design factors). If so, five tables will
do:

Units
UnitNo <Primary Key>
(fields pertaining to the unit as an entity, such as name, text description,
....)

Models
ModelNo <Primary Key>
Description
<other fields about the model as an entity>

DesignFactors
FactorID <Primary Key>
Description

UnitModels
UnitNo <link to Units>
ModelNo <link to Models>
<any needed info about this model for this unit - price maybe?>

ModelDesigns
UnitNo <link to Units and also to UnitModels>
ModelNo <ditto>
FactorID <link to DesignFactors>
<any info about this triplet combination>

This final table is your "3-D array", and there is in principle no problem
setting up a ten-dimensional array if you need one. That's how relational
tables work!

John W. Vinson [MVP]
 
You certainly make the problem sound so simple!
I think I need to dwell on this a bit!
Is there some place I can find an examples of designs for a form and
queries that interfaces with such tables?
 
Is there some place I can find an examples of designs for a form and
queries that interfaces with such tables?
Click to expand...

Many. You can start with the Northwind sample database which came with your
installation of Access; there are lots of other examples, tutorials and
resources available. Start with:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]
 
Back
Top