Inventory Database Design

[dylcon]

Hello, I have to keep records for individual parts within an inventory. First, I will supply the sufficient background information:

There are roughly 1,000 parts that I will be keeping track of; some of which the inventory amounts change nearly every single day, and others which only come through the plant once. These parts are divided into three main categories: metallize, pad print, and paint.



As of now, I was planning on setting up some forms so that when a part comes in, the user can simply enter in a few fields of information that I will require for record keeping. I was planning on having the data go to one of three tables: 'metallize', 'pad print', or 'paint' depending on the type of part that it is. When it comes time to see the trail of a specific part, I was planning on having another form that will allow the user to pick the part number, and a report that shows every incoming/outgoing shipment for that part number back to a specific date.

Does this seem like a reasonable approach? If not, I would love to hear some suggestions.

P.S. I have already looked at the template for inventory that MS Access provides, but it has a surplus of options that I do not want. If anyone knows how to reduce this template, I could potentially do that as well.

Thanks!

[Dal Jeanis]

In general, the only reason to split those three "part types" into separate tables is if they have vastly different information requirements. A better approach is to put them in a single table that has the information common to all parts, and then have separate tables for the information that is not common.

It also looks like you may be confusing "what we're doing to the parts" with "the parts themselves". Your three tables sound like the names of internal processes. When you add a fourth process, then, you would be forced to add a new table and new infrastructure to support it. Also, if that new process could be done in conjunction with any of hte other three, you'd have to redesign the whole database.

For an overview and some practice examples of how to think about database design, check out this site by Access MVP Roger Carlson http://www.rogersaccesslibrary.com/
Access MVP Crystal Long has also provided an excellent overview, precise methods are illustrated with Access 2003, but an easy read. http://www.accessmvp.com/strive4peace/

Take a whack at defining and normalizing your data using Carlson's method, and you should feel a whole lot more confident about your design.

[dylcon]

After reading that, I have come up with this setup. Does this look like it could work?

[dylcon]

In addition to this, I would to be able to do essentially two different tasks with this database.

1) I would like to be able to generate a report that shows all of the "active parts" in the inventory.
By "active parts", I mean anything with an on-hand quantity other than zero.
I was planning on having another table that would calculate the total on-hand quantity by subtracting the 'OutQuantity' from the 'InQuantity'. This table I will be able to run a search on so I can then generate a report that would find anything with an on-hand quantity > 0, and then show the relating fields to this specific part on this report.

2) I would like to be able to generate a report that shows all transactions (incoming, adjustments, and outgoing) for a specific part. For this, I was planning on searching for the part number... and then I am not exactly sure what to do next.

Finally, I am unsure if I should have the PartNumber as the Primary Key for my Part table. I want to do this intuitively, but I have read to always have an AutoNumber, which is what I have now.

Thanks in advance for any answers!

[Dal Jeanis]

Your third question is the easiest - index the part number, but don't use it as the primary key. Also, each of your transaction files should have an autonumber primary key, in addition to the foreign key to the part record.

Your second question is not difficult. You can create a query that gives the UNION of all incoming, outgoing and adjustment records, Ordered by part number and date. Then you can do a summary report on that query. be sure to account for the sign of the outgoing parts.

Let me suggest something - if you put Incoming, Outgoing, and Adjustment in the same table, with a flag for which kind of record it is, you simplify your life immensely. Outgoing will have a negative Quantity. Adjustment can have positive, negative or zero. That way, a simple query on part number with Sum(Quantity) will get you the number of that part that you supposedly have in stock right now.

Optionally, you could implement a "Prior Inventory" type, and periodically, for instance every quarter or year, you can move all prior detail records to a history table and replace them in the current table with a single "prior Inventory" record. (This could be a subtype of Adjustment if you want).

HOWEVER

I don't think your design is correct yet. The IncomingID, OutgoingID and so on links don't make sense to me - they are kind of backwards. One Incoming part can have many part base records? One part base record can only have one incoming record, and one outgoing record, and one adjustment record?

If you merge the three files as I suggested above, then I think the actual design will end up having each transaction record has a foreign key to one part base record (many to one). You don't need a FK on the part base record to the transaction record that way.

Walk through another of Carson's examples, then take another whack at the design.

[dylcon]

That helps very much, Thank you. What exactly is a "'Prior Inventory' type"?

Also, one part can have many different Incoming, Outgoing, and/or Adjustment (s) because many part numbers are run more than once through our operations. But at the same time, not every single part number will necessarily have each of these fields populated, which is another reason why my previous design does not work.

How about ths? I think this makes more sense.

[Rainlover]

I don't know if this is of any help.

At least it is a working example that I think meets your needs.

[dylcon]

Does this look any better? Access says I cannot make a relationship between any of the incoming/outgoing/adjustment detail tables and its respective date table. It says I am violating reference integrity rules. Any idea what I am doing incorrectly?

[Rainlover]

The three Tables on the right that store Dates are not required at all. The Date can be part of a higher Table.

The table in the Middle has several field forming the primary key. You only need one Primary Key in any Table provided you are using Autonumber. I believe you have your Primary and Foreign keys back to front.

[Rainlover]

The three Tables on the right that store Dates are not required at all. The Date can be part of a higher Table.

The table in the Middle has several field forming the primary key. You only need one Primary Key in any Table provided you are using Autonumber.

I do understand why you have a separate Table for dates. You are thinking that this is a field that repeats. But really it does not. You may use the same Date a few times but it is not like a City, a Profession a School Subject which are used time and time again.

Just type the date in the appropriate table. A date after all is just a number and you would not create a separate table for numbers.

EDIT

I did something wrong with my save. Thought it best to leave both as they are slightly different

The save is playing up for me. Maybe a slow connection.

[Dal Jeanis]

In first-normal form, each table contains information about only one ENTITY. One THING.

It seems to me that the Part table has some problems. I can't manage to make a business case for that particular information having a sensible meaning about ONE ENTITY. But, I could be completely wrong because I might misunderstand your company's business process.

I'm almost positive the one-to-many relationship between part and transaction is backwards.

MY ASSUMPTIONS ABOUT YOUR PROCESS

You receive a contract to finish parts for one of your clients. The PO is entered into the POInfo table, linked to the customer, and each of the parts to be handled for that PO is entered into the Part table, linked to that PO.

Customer <-->> POInfo <-->> Part

Each part has a PartType property which is one of three allowable Process Types: metalize, pad print or paint.

Part Type <->> Part

When you receive a shipment of part(s) for a particular PO, you enter the transaction record to show their arrival. When you send them out, you enter a transaction record to show their shipping out. If some of that part are lost
or spoiled or turn up in the back of a closet, you enter a transaction record to show the difference.

Part <-->>Transaction

Now, I have a question here - do you want to have shipping records, inbound and outbound, which show all the different parts that arrived or left in a particular shipment? Do you want a single shpment record, regardless of whether the parts were regarding a single PO or multiple POs?

ShippingRecord <->> Transaction

If I'm wrong about the above, then please do the following:

1) First, please describe the process by which parts come into your shop, are handled, and shipped out. This information will help me see the entity relationships.

2) Next, please describe for me what *entity* each of the tables is supposed to hold information about. Use as many words as necessary - don't skimp, because that leads to you not knowing some of your own assumptions.


If I'm right about the above, then here's a rough design:

tblCustomer
CustomerID (PK-Autonumber)
CustomerName
CustomerAddress/Etc

tblPOInfo
POID (PK-Autonumber)
CustomerID (FK to tblCustomer)
PONumber
PODate
POExpirDate
PONotes

tblPart
PartID (PK-Autonumber)
PartNumber (indexed, allowdups?)
PartDesc
PartTypeID (FK to tblPartType)
PartNote
POID (FK to tblPOInfo)

tblShipment
ShipID (PK-Autonumber)
ShipDate
ShipType (FK to tblShipType)
ShipNote

tblTransaction
TransID (PK-Autonumber)
ShipID (FK to tblShipment)
PartID (FK to tblPart)
Quantity (NOTE - outgoing quantities are NEGATIVE)
TransNote

And Two Code Tables

tblPartType
PartTypeID (PK-Autonumber)
TypeDesc

tblShipType
ShipTypeID (PK-Autonumber)
ShipDesc NOTE - (incoming, outgoing, adjustment)

Now, I want to point out that the Customer ID belongs on the POInfo, and not on the individual parts or shipments or transactions. This is a referential integrity thing - if you copy the customer ID across to all the other records, then if someone goes in and changes the customer ID on any of the middle records, you will get different results, all erroneous, depending on how you coded your query.

I have this nagging feeling that the Part table is still not quite right, but I'd have to ask you a bunch of business process questions to find out why or to make myself more comfortable with it. The only concrete thing I can point to is that, since different companies might coincidentally use the same PartNumber, and since the same company can have different POs using the same PartNumber, there might be some additional design considerations (and process considerations) for dealing with that.

"I'm shipping out 25 type-3 widgets to ABC company. There are two different open ABC POs with type-3 widgets. Which one is this for?"

[dylcon]

Rain, thank you very much for your contribution.

Dal, here is a description of what I am trying to do:

The part comes in from a customer. Sometimes, it is a new PO, but many times a customer just keeps an "open PO number" so that we can make many parts go through ou process under one PO number. Just to clarify, this means that we could get a shipment of type-3 widgets on Monday with a PO# of 1234 and receive the same part on Thursday with the same PO#. In addition to this, we also can receive multiple parts under one PO. For example, on Monday we recieved a type-3 widget and a type-1 gizmo all under PO# 99. Although the majority of the shipments will be a part that we know on a new PO#, it is not exclusively true. This is why I am concentrating more on the part number and using the PO more as a sidenote.

My ultimate goal is to be able to quickly print off an invoice at the beginning of the day and also be able to search for all transactions of a part (includes: incoming, outgoing, any adjustments for error). This being said, some of the information (such as part type is only going to be used to organize the printed off invoice sheet.

Now, I have a question here - do you want to have shipping records, inbound and outbound, which show all the different parts that arrived or left in a particular shipment? Do you want a single shpment record, regardless of whether the parts were regarding a single PO or multiple POs?

I want the shipping record to show just transactions for that part - not everything under that PO. Maybe it would be possible to show both with a query when I am generating a report, but it is not entirely necessary. And yes, I want a single record, regardless of whether it is under one PO or multiple PO's. But once again, upon report generation, I would be able to see all of the PO numbers for each transaction.

Finally, here is my best attempt at describing each entity:

For customers, all I need is a customer name. The only time this will be used is as a sidenote essentially, as well as a filter for when I am using comboboxes on the forms I will be creating.

For PartType, all I need is a way to distinguish the parts from metallize, paint, or pad print.

For POInfo, I will need the PO number, the POdate (the date it needs to be completed by).

For each Part, I will need the customer, the Partnumber, the description of the part.

And this is where I get confused... I am not sure if the incoming, outgoing, and adjustment portions should be their own tables or not.

I am leaving this update, but I have posted additional, more useful information below.

[dylcon]

Dal, here is a more thorough description (along with some adjustments) of what I am doing.
I have written down all of the entities and attributes and tried seperating them and this is what I have come up with:

All information required: Part Number, PO Number, PO Date, Incoming Date, Scrap Quantity, Incoming Quantity, Outgoing Quantity, Process Development Quantity, Non-Conforming Materials Quantity, Red/Green Quantity (basically scrap that is still shipped out), Customer Name, On-Hand Quantity, Part Description, Part Type, Outgoing Date, Employee Inititals, Employee Name, Shipping Notes, Transaction Notes, and Customer Notes.

Grouped into:

PartInformation
PartNumber
PartDescription

POInformation
PONumber
PODate

Transaction History
ScrapQuantity
IncomingQuantity
OutgoingQuantity
ProcessDevelopmentQuantity
NCMQuantity
RedGreenQuantity
Notes

CustomerInformation
CustomerName
Notes

EmployeeInformation
EmployeeName
EmployeeInitials

PartType
PartType

ShippingInformation
IncomingDate
OutgoingDate
Notes

Relationships:

1. Each part can have one or more transactions. Each transaction can have one and only one part.

2. Each PO can have only one customer. Each customer can have one or more PO's.

3. Each part can have one part type. Each part type can have one or more parts.

4. Each part can have one or more shippinginformation. Each shipping information can have one part.

5. Each shipment can have one transaction. Each transaction can have one or 0 shipment.

6. Each part can have one or more PO. Each PO can have one or more part.

7. Each employee can have one or more transactions. Each transaction can have only one employee.

Comments about relationships: The employee entity will just be used as a sidenote to see who entered it into the system, or who checked that that quantity was changed.
The realtionships #1 and #4 are a little confusing to me as well. This is because in real life, each transaction or shipment can be done for multiple parts, but as far as I am concerned, I only need to worry about one part at a time so I disregarded this. If it is to account for this many-to-many relationships, then I may as well construct the realtionships in the database assuming: Each Part can have one or more transactions and/or shipments. Each transaction/shipment can have one or more parts.
The other concern I have is with relationship #5: how do I incorporate the fact that one transaction may not be an actual shipment? Is that where the code tables that you suggested would come into play?

I will keep working on this and post any udpdates that I may have, but as of now, thank you so much for all of your help and great suggestions.

[Rainlover]

May I suggest that you build you tables in Access as you see them now.

Also create your Relationships to see if it works for you.

Then you could post a screen dump of the relationships for further help/comments.

[Dal Jeanis]

1) Here's some big picture questions -

I noticed you now have four different reasons for adjustment of number of parts (Scrap, NCM, RedGreen, ProcessDevelopment). Is your real purpose of this application to track the working of the parts, or something else?

You talked about wanting to print off an invoice at the beginning of each day. Did you mean invoice, or tracking slip, or what? You haven't yet provided for any billing or payment tracking in your database, so this worries me...

When a large shipment of parts arrives, are they broken into batches, or not? If so, are the batches numbered or tracked in any way?

2) Given your way of viewing the business need, it may be safer for you to create three separate tables for incoming, outgoing and adjustments. That way, you fully understand what information is in each table, and you will handle that information appropriately in yoour design and code. (What I would do in your place is totally irrelevant - you should do what will work for you.)

3) The shipment versus part confusion is a little like the difference between the individual lines on a packing slip and the shipment information at the top of the packing slip itself.

A "shipment" has one date, one source/destination company, and a little more identifying information.

A "shipping item" has one part number, one quantity (and a weight and a price you don't care about).

The natural way to represent this is a table for the "Shipping" part of the shipment, and a different table for the "Items Shipped" portion of the table.

Same division on the incoming side. Adjustments, maybe so, maybe not, depending on how you see the business operations.

4) I noticed some wording that made me think you consider the PO date to be the "due date" for the parts. Ideally, you should make a field name clearly express its usage. In real life, a PO might have the date the PO was written by the originating company, an expiration date, and several other relevant dates and terms.