Problem with normalization

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BobC - 04 Feb 2008 01:02 GMT

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

Reply to this Message

John W. Vinson - 04 Feb 2008 01:27 GMT

>We will likely be using Access 2007 ... We currently have Access 2000.

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.

>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.

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]

Reply to this Message

BobC - 04 Feb 2008 02:03 GMT

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?

>> We will likely be using Access 2007 ... We currently have Access 2000.
>
[quoted text clipped - 50 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 04 Feb 2008 03:46 GMT

>Is there some place I can find an examples of designs for a form and
>queries that interfaces with such tables?

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]

Reply to this Message