Advice on table design strategy?

[DougW via AccessMonster.com]

I am working my way through my first Access project in many years.
I could use some advice on how to proceed.

The database is set up to track ordered items. We have a pre-defined set of
all possible components we might order (this in a table called Components).
There is another table of PurchaseOrders (basically just a list of unique PO
numbers we have issued).
These entries in these two tables were used to populate a very large table
called OrderedItems.
The OrderedItems table has a record for each individual Component ordered
along with the PO it was ordered on.

All these tables and supporting forms are now in place and reasonably
functional.

Now I need to collect OrderedItems into Assemblies. An assembly will be a
unique identifiable physical collection of parts(OrderedItems) that will be
assembled together and delivered to a customer. There will be a finite set
of permutations of components defining a particular Assembly. I am
considering adding another table called Configurations. A Configuration
would be the "recipe" that dictates which Component types, and what quantity,
will go together.

For example, Configuration #1 might consist of (1) piece of Component (type)
#15, (2) piece of Component #18, and (1) piece of Component #20.
Configuration #2 might consist of (2) pieces of Component #7 and (1) piece of
Component #12. A given Configuration will be the basis for many Assemblies.


Note that I will not always have the same total number of parts (components)
for the different Configurations. Here is where I am getting hazy. Should I
structure my Configuration table with some arbitrary maximum number of
defined components in mind?

Example A: Here is how I might structure the Configuration table for a
maximum of (4) unique components:
ConfigIndex (primary key)
CompAType
CompAQty
CompBType
CompBQty
CompCType
CompCQty
CompDType
CompDQty

I guess this might work if I am allowed to leave some of the fields blank in
some cases (?)

My other option might be to structure my Configuration Table as follows:
Example B:
ConfigIndex (primary key)
ConfigType
CompType
CompQty
-- where I define ConfigType (an integer) in a separate small table. In
this case the records would have less fields, there would be no blank fields,
but I would have more records. I might have 3 records in a row defining
ConfigType #1, then the next 4 records defining ConfigType #2.

Once I have the Configuration table set up, I would like to be able to use it
to populate my Assembly table. Assembly #1 might use Configuration #17. I
would like to use the Configuration table to control a form and query which
would allow me to select only the required (and available) Components from my
OrderedItems table. I have an "Assembly" field in my OrderedItems table
which will link each physical item in OrderedItems to a particular Assembly
index.

Again, the Configuration table could be thought of as the total list of
"recipes".
The Assemblies table will describe physical unique assemblies that bring
together collections of Ordered Items.

I hope this was not too rambling and it makes some sense.
I would appreciate any comments or ideas on how I should handle the
"Configurations" (using Example A, Example B, or some other method). At this
point Example A seems like the prudent way to go, to me...

Thanks in advance for your suggestions!
- Doug

 

[Jeff Boyce]

Doug

If you try to set up a maximum number of components, the universe (or your
boss) will find a way to exceed that number by one <g>!

And since one Assembly might have two components, while another might have
5, you are better off dropping the spreadsheet (add another column!)
approach and using a relational (1 assembly can have 1-many components)
design.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Evan Keel]

I am working my way through my first Access project in many years.
I could use some advice on how to proceed.

The database is set up to track ordered items. We have a pre-defined set of
all possible components we might order (this in a table called Components).
There is another table of PurchaseOrders (basically just a list of unique PO
numbers we have issued).
These entries in these two tables were used to populate a very large table
called OrderedItems.
The OrderedItems table has a record for each individual Component ordered
along with the PO it was ordered on.

All these tables and supporting forms are now in place and reasonably
functional.

Now I need to collect OrderedItems into Assemblies. An assembly will be a
unique identifiable physical collection of parts(OrderedItems) that will be
assembled together and delivered to a customer. There will be a finite set
of permutations of components defining a particular Assembly. I am
considering adding another table called Configurations. A Configuration
would be the "recipe" that dictates which Component types, and what quantity,
will go together.

For example, Configuration #1 might consist of (1) piece of Component (type)
#15, (2) piece of Component #18, and (1) piece of Component #20.
Configuration #2 might consist of (2) pieces of Component #7 and (1) piece of
Component #12. A given Configuration will be the basis for many Assemblies.


Note that I will not always have the same total number of parts (components)
for the different Configurations. Here is where I am getting hazy. Should I
structure my Configuration table with some arbitrary maximum number of
defined components in mind?

Example A: Here is how I might structure the Configuration table for a
maximum of (4) unique components:
ConfigIndex (primary key)
CompAType
CompAQty
CompBType
CompBQty
CompCType
CompCQty
CompDType
CompDQty

I guess this might work if I am allowed to leave some of the fields blank in
some cases (?)

My other option might be to structure my Configuration Table as follows:
Example B:
ConfigIndex (primary key)
ConfigType
CompType
CompQty
-- where I define ConfigType (an integer) in a separate small table. In
this case the records would have less fields, there would be no blank fields,
but I would have more records. I might have 3 records in a row defining
ConfigType #1, then the next 4 records defining ConfigType #2.

Once I have the Configuration table set up, I would like to be able to use it
to populate my Assembly table. Assembly #1 might use Configuration #17. I
would like to use the Configuration table to control a form and query which
would allow me to select only the required (and available) Components from my
OrderedItems table. I have an "Assembly" field in my OrderedItems table
which will link each physical item in OrderedItems to a particular Assembly
index.

Again, the Configuration table could be thought of as the total list of
"recipes".
The Assemblies table will describe physical unique assemblies that bring
together collections of Ordered Items.

I hope this was not too rambling and it makes some sense.
I would appreciate any comments or ideas on how I should handle the
"Configurations" (using Example A, Example B, or some other method). At this
point Example A seems like the prudent way to go, to me...

Thanks in advance for your suggestions!
- Doug

Jeff is right, avoid repeating groups. What I am not following is the need
for a Configuration table Why can you just create and AssemblyComponets
table with all legal Assemblies and the quantity of acomponent for that
Assemply. What am I missing? Also, what exactly appears on your Purchase
Order?

Evan

 

[DougW via AccessMonster.com]

Jeff, thank you for the advice. I will try to structure it as you
recommended (similar to my Example B). I also agree with your insight on the
behavior of the universe and bosses

Evan, I should clarify a bit, I probably left out some relevant details.
The Purchase Orders are orders where my company orders machined components
used in our equipment. We take the parts from various POs and assemble them
into Assemblies used by our customers ("customers" within our company
actually). The Purchase Order table is basically just a list of our POs we
have issued and the vendor's name. It's relatively small.
We can have many parts ordered on a single PO.
Parts from one PO can end up on many different Assemblies. Those parts are
line items on the POs - the individual parts show up in my OrderedItems table.
That's the really big table in my DB. I think of that table as being the
"middle" of my DB.

The list of unique part types (think drawing number) are contained in my
Components table.
The list of all physical parts ordered (think serial number) are contained in
my OrderedItems table.
The records in OrderedItems point to the part type in the Components table
and the PO in the PurchaseOrder table on which that particular item was
ordered.

The Configuration table would be similar to the Components table in that it
would define "types" or "models" of all legal assemblies. The Assemblies
table would be similar to the OrderedItems table in that it would contain
records of actual physical assemblies produced.
An analogy would be that the Configuration table would be a list of car
models - the Assemblies table would be a list of VIN numbers of actual cars
produced.

My vision is to somehow arrange this so that I can populate my Configuration
table with the definitions of all my legal assembly types, containing the
Components (part types) used for that configuration.
Then when working from a form linked to my Assemblies table, I would like to
be able to select a configuration for a particular assembly, which would then
generate a list of those available (unassigned) OrderedItems that are needed
for that configuration. Kind of picking parts from baskets and putting them
together in another basket - and I would only be presented with the "baskets"
(lists) appropriate to my desired configuration (maybe not a great analogy).

I referred to available or unassigned OrderedItems. I have a field in
OrderedItems reserved for the Assembly number. So the result of my form
transaction that I described in the previous paragraph, would be that a
number of OrderedItems records would be updated with one particular Assembly
number.

The purpose of the whole DB is to allow us to correlate all the parts we
order on the PO's, to the Assemblies that we eventually deliver to our
internal customers. We should be able to run queries or reports that will
use the promised ship dates of parts from our vendors on PO's, and calculate
the resulting projected delivery date of a given Assembly (looking at the
latest shipping date of all the parts contained in that Assembly), for one
example.

I hope that made some sense. Maybe there is some way to incorporate the
Configuration function into the Assembly table as you suggested? I'm wide
open to any suggestions.

Thank you very much for your guidance, it is truly appreciated!

-- Doug

I am working my way through my first Access project in many years.
I could use some advice on how to proceed.

[quoted text clipped - 75 lines]

Thanks in advance for your suggestions!
- Doug

Jeff is right, avoid repeating groups. What I am not following is the need
for a Configuration table Why can you just create and AssemblyComponets
table with all legal Assemblies and the quantity of acomponent for that
Assemply. What am I missing? Also, what exactly appears on your Purchase
Order?

Evan