normalized table structure

  • Thread starter Thread starter BAM137
  • Start date Start date
B

BAM137

I need to create a properly normalized table structure for a parts
inventory/machine maintenance database and I am stuck on how to normalize and
relate the parts tables.

The details:

I have two machines that each have unique parts yet share other parts in
common.

I have several part suppliers and I am liable to buy a part from one this
time and the same part from a different supplier the next time. Each supplier
has their own part numbers for what is to me the same part.

example:
PartA on MachineA
Supplier1 pn#123
Supplier2 pn#456
Supplier3 pn#789 ... and so on...

It's highly unlikely, but not impossible, that two part numbers from
different suppliers would be the same.

To me it seems that I should create my own unique part numbers (autonumber)
and use the supplier's part numbers as a descriptive field in the parts
table. Then it seems as if my parts table fields are redundant. The other
approach that I have toyed with is to create a record for each part using the
supplier's part number. This has the potential, although unlikely situation I
noted above and it also seems to me redundant in that PartA will now have
multiple records depending on how many different suppliers offer that part.

Has anyone "been there, done that"? Any and all ideas are gratefully received.

Thanks so much.
 
BAM137 said:
It's highly unlikely, but not impossible, that two part numbers from
different suppliers would be the same.
Click to expand...

Ayup. And the duplicate part numbers could be on two totally
different machines from different manufacturers as well.
To me it seems that I should create my own unique part numbers (autonumber)
and use the supplier's part numbers as a descriptive field in the parts
table. Then it seems as if my parts table fields are redundant. The other
approach that I have toyed with is to create a record for each part using the
supplier's part number. This has the potential, although unlikely situation I
noted above and it also seems to me redundant in that PartA will now have
multiple records depending on how many different suppliers offer that part.
Click to expand...

Another solution would be to create a parts table as you already have
with the original part number on it. Then create a parts number alias
table which has the vendor on it as well as the new vendors part
number. Then use a UNION query on the various combo boxes pulling in
data from both tables.

However this gets complicated because you really want to put the other
vendors quantity into inventory based on the original vendors part.
After all they are, supposedly, the identical part with, supposedly,
the same durability. Just a different box.

(I say supposedly because it's been my experience that sometimes third
party parts will fail a lot sooner than the original mfr part.)

And how do you handle the duplicate part number issue? I've been
thinking that either the combo box as a part type and vendor on it so
you can choose the correct part or you have to pop up another form
allowing the user to choose.

This last bit gets easier if the parts have a UPC code as that should
be more unique than a part number.
Has anyone "been there, done that"?
Click to expand...

Not quite yet but I will be in a month or less. So I'm still
thinking on this particular issue.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
You need to keep your inventory separate from your parts master. The parts
master should have nomenclature and specifications for the part as well as
YOUR part number. A child table should contain the Supplier, the supplier's
part number, and the unit cost. Then you have the inventory side of things
where you keep track of what you have in house, who the supplier is, what
his part number was when you ordered it, what his price was when you ordered
it, and how many you have on hand.

Are these serialized parts? If so, the serial number is kept in the
inventory record and the quantity will be 1 for all serialized parts. The
inventory record also contains such information as warranty expiration or
expiration date in the case of items that have a limited lifespan.

These ideas are from large mainframe inventory applications where there were
potentially hundreds of thousands of parts to manage and multiple warehouses
and storage locations within each warehouse so some of this might be
overkill for your purposes. Except for keeping inventory separate from the
parts master. Trying to manage these as one table just doesn't work.
 
Back
Top