Inventory Database Design

[Rainlover]

Dal

What your latest design reminds me of Allen Browne's Inventory.

The following link has a pic of his relationships plus some other ideas.



It might be worthwhile for you to have a look. It might give you some additional ideas.

http://allenbrowne.com/AppInventory.html

[Dal Jeanis]

Awesome, Rain! That's very similar to what I'd be aiming for. An elegant example of an appropriate design.

I'm getting the feeling that dylcon needs something as simple as possible, and also that there are some business needs that haven't been articulated yet. That's why I put my business analyst hat back on and asked more questions...

[Rainlover]

Dal

Would you like a working sample Database.

[dylcon]

Here is what I have come up ^


Dal,
1. The purpose of this is to be able to tel exactly what happened to each part's on hand quantity at a specific time. Those are all specific adjustments, so instead of just knowing that there is an adjustment within our own operations, this way we can tell exactly why this happened.

What we will use this "invoice" for every day is to show exactly which parts will be shipped out that day. It works relatively funny because some customers do not give us an actual deadline. They will just send an email saying that a truck is coming that day and we will send out x amount of parts. Other customers require a specific amount and date that their parts be sent out. This is why the "invoice" is more like a schedule, simply showing each customer's active parts, the on-hand, shipped, etc. quantites, and the PO# and PO date. Some of these dates will be "next truck". Others will be an actual date that we need to follow like I was describing previously.

When a large shipment of parts comes in, they are seperated by part number. NOT by PO#, which many places do. Sometimes, it may be one PO# that covers several different parts at once.

2. The three seperate tables is what I originally had, but I think you said to keep them all together for calculation purposes, which I thought was a good idea as well.

3. I think I may have accomplished this during this most recent database design.

4. I have specified a difference between a "due date" and a PO date among my attributes, sorry about the poor wording. Thanks for looking out though.


Once again, thank you for all of your help!

[dylcon]

Dal and Rain,

Would it work best if I replaced my tblTransaction, tblShipment, and tblShipType with something like the relationship that Rain's database has? So instead I would have a tblAcqDetail, tblAcq, tblSalesDetail, tblSales, tblStockTakeDetail, tblStockTake equivalents. Would you agree that this would be the ideal design for what I am looking for?

[Rainlover]

My Sample Database in Post #7 is based upon Allen Browne's

It is really up to you to decide if it can be applied to your situation. The main advantage of this system is that it give the stock on hand based upon ins and outs plus the latest stocktake. This is one situation is one that allows you to store a value rather than calculate the balance. Although the stocktake value is derived form outside the Database. Namely a manual count.

Dal appears to be helping you very well. I just thought you needed some more direction.

I will let Dal continue and only jump in if required.

[dylcon]

Thanks a lot Rain. The stocktake value is a very useful tool, but I do not believe that it would be as applicable in my situation. Your sample database has helped immensely, and I really do appreciate that.

[Dal Jeanis]

Rain - it's dylcon's design we're working on.

dylcon - I think you're pretty close! Here's one biggie, then some nits -

(1) You can have the same part number twice in the same company, for different POs. Given this reality, I don't think it will be possible to be certain which of the duplicate POs a particular outgoing shipment was due to. You might want to consider linking the tblTransaction record to the tblPartPO record, rather than the tblPartInfo record.

(2) Does the PODate and POExpirDate belong on the tblPartPO record or the tblPOInfo record? Is there a different date that belongs on the tblPartPO record (say, duedate?)

(3) You can shorten all the Description fields to Desc and save yourself typing.

(4) In actual usage, will the same employee be entering all the info for a single shipment received or sent, at the same time? If so, move the employeeID to the Shipment table.


(5) This is the point where I tell people: "Now, forget the exact words you were using, and describe each entity afresh in the best words you can now find to represent what entity is actually going to be stored there."

For instance, a transaction is "A record that represents a single change in the number of a single kind of parts, and describes the reason for that change."

The most questionable word I see is "Shipment", since that table will hold incoming shipments received, outgoing shipments sent, and four kinds of adjustments that aren't related to shipping at all. The true entity is "a group of individual transactions (as defined above)". A group or list of related transactions is often called a "batch", so you might consider using tblBatch as the name for your tblShipment table. You can still use the word "Shipment" on the forms that will be used to enter the tblBatch records and the transaction records for incoming/outgoing shipments.

[dylcon]

Dal,

It is nice to hear I am finally getting closer on this.

1. As far as where the transaction table is linked, does it matter that there will also be multiple parts under one PO? I don't understand why this would be better. Would it be possible to make PartPOID a FK on the transaction table in addition to the PartID? That way it would be linked to the part and the PO. Or would this just cause problems?

2. I was considering the duedate as the POExpirDate so I should move the PODate to the POInfo table.

4. The same employee will not be entering in all of the info for a single shipment. More than likely this will be the case, but in case they don't I thought it would make more sense to have it connected to the Transaction table. Another reason I did this was so each internal adjustment would be inititaled as well.

5. I will go back through and look at my names for everything.

One more question: which design, from post #19 or post #20, seems to work better in the long run? I see how both of them would work, but if I am going to start building forms, I should have the best design possible.

[dylcon]

One other question I have Dal...

How will I be able to eliminate user input such as entering a new comapny when that company already exists or entering a new part number when that part number exists?

[Dal Jeanis]

1) Your first question gets a question back - do you EVER need to know precisely which PO an incoming shipment or outgoing shipment belonged to?

Certainty on that information would not be possible if you link through the tblPartInfo record for a part that is on multiple POs. The one-to-many connection on each side of the tblPartInfo record guarantees an ambiguity that cannot be resolved with certainty. This situation will also result in some versions of a reasonable-looking query giving you multiple lines back for a single transaction record - one linked to the first PO record, one linked to the second.

If you link the Transaction to the PO, that ambiguity will never arise.

VISUAL METAPHOR - Imagine a butterfly of data, with the part record at the center. Items on the left wing (Transaction) may or may not be related to items on the right wing (PO). This is because travel from the left wing is always to a single record until it gets to the part record, but then starts linking to multiple records as it continues to the PO wing.

On the other hand, if you move the Part record to the far right, on the other side of the PartPO record from the Transaction record, then you have only a single wing (fan), and travel from left to right will always be to a single record.

2/4/5) Good Enough.

6) Your last question is the easiest. There is no way to know. Either design can do what you want, each has advantages or disadvantages in certain items you will code, and only time and practice in the actual usage of the database will tell you which design factors are [i.e. would have been] most important.

I'd spend longer and be more careful with my professional opinion if there were real money fields -- and therefore auditing considerations -- involved, but from my experience, the two architectures are roughly equivalent, and the selection is arbitrary.

I'd tend to go for the single table, with the forms handling the work of making sure that the appropriate sign was stored on the quantity - positive for incoming, negative for outgoing and most adjustments.

For you, though, the most important consideration has to be that the design properly reflects the way you understand the data. You're the guy that has to debug the app whenever it does something weird, and they ALL do something weird occasionally. For a beginner, it might be more natural and supportable to keep the three tables separate, and do the reporting based on various UNION queries as needed.

[dylcon]

1) Yes, I guess I would need to know precisely which PO a shipment of parts belonged to. This is because when I generate a report to look at all thing affecting the inventory of a part back to a certain time period, it would be necessary to see which PO (or at least the date of the incoming/ outgoing shipment).

So knowing this, I should link it to the PartPOID? Doing this, my relationships now look like:

And of course I will rename the fields and delte unnecessary ones.

[Rainlover]

One other question I have Dal...

How will I be able to eliminate user input such as entering a new comapny when that company already exists or entering a new part number when that part number exists?

In your Table for that field you create a Index with duplicates Not Allowed. There is some code that you can use with your Combo Box Event "On Not On List" that will allow you to add a New company etc, because you also set the Combo box to limit to the List. This way during set up if you miss a Company you can add a new one without closing that form and opening a new one.

As a negative statement, if you need to enter/store Negative Numbers then your design is incorrect. This should never happen. The only negative numbers you should ever enter are number that are indeed negative.

Also if you need to use Union Queries then again your design in wrong. I have never used one and I mean NEVER. In fact I would not know how to create one. A Union queries prime purpose is to correct bad table structure in the tables. Usually in the situation where you have no control of the Data. That is either someone else wrote the Database and you are trying to do something with it or the Data is imported from a different Database.

[Dal Jeanis]

IMPORTANT Note - this was NOT a reply to Rain's post #28.

Dang. Just lost a long post to a backspace. Sigh.

dylcon - You're done. Once you drop the unneeded fields from tblPOInfo, and adjust the naming conventions as you choose, you have a workable db schema.

It's now set in oatmeal. Concrete comes after a couple more steps.

Next, sketch out the screen flow diagrams that show how each user will get what they need from the database. Look at each screen (form) on your flowchart and see where the information is coming from and going to. Make sure you know how to give and get what your users need.

This is the step at which (next time) you will find the ambiguity I pointed out to you in 23 and 26. This step is also where you will answer for yourself any questions like you asked in 25.

COMPANY SELECTION

Originally Said:

Off the top of my head, what I would do for company is this - use an unbound combobox pre-loaded with the existing company names in alpha order, but NOT limited to the list. If the user enters anything that isn't on the list, pop up a modal (but escapable) entry screen to enter the new company name data. once the new company is validated, save the record, reload the combo box and select the newly added company record.

Should be Replaced By:

As Rain said, use a bound combobox pre-loaded with the existing company names in alpha order, limited to the list. If the user enters anything that isn't on the list, use the NotInList event to pop up a modal (but escapable) entry form to enter the new company name data. Once the new company is validated, save the new company record, reload the combo box and select the newly added company record in the combo box, then close the modal form.

Make sure that your entry screens force the user to select the company FIRST, so you can limit the other combo boxes to THAT company's POs and Parts. See "cascading combo boxes" for info on this, and post any questions in a new thread.

PO/PART SELECTION

You can use the same method for POs and Parts, although a little bird is itching me about parts, and I don't know why. Try it and see. Ah - part numbers have duplicates allowed. You'll need to be careful to make sure...

DING...

You must establish a multi-column unique key for POinfo (customer-PO) and for PartInfos (customer-part). This will ward against accidental duplication of PO number or part numbers.

Crap. That means duplicating the customerID as a field on the PartInfo, which I told you not to do.

You SEE why I said it's set in oatmeal? Data base Usage beats data base theory every time.

Let's defer that and move it to a different thread for open discussion on why you should or shouldn't have customer ID on the other tables.

We're done here.

[Rainlover]

COMPANY SELECTION

Off the top of my head, what I would do for company is this - use an unbound combobox pre-loaded with the existing company names in alpha order, but NOT limited to the list. If the user enters anything that isn't on the list, pop up a modal (but escapable) entry screen to enter the new company name data. once the new company is validated, save the record, reload the combo box and select the newly added company record.

Make sure that your entry screens force the user to select the company FIRST, so you can limit the combo boxes to THAT company's POs and Parts. See "cascading combo boxes" for info on this, and post any questions in a new thread.

You must have your Combo Bound. What is it going to do if it isn't bound to a Field in the Record Source.

It must be set Limited to list otherwise you can free type anything you like. If it is limited it forces you to add it to the list so that it is there next time it is required.

If it is set to Not Limited there will be no trigger to open your Pop Up as you call it.

Dal, you have been very helpful to the OP but ignoring other peoples comment/help does nobody any good.

I am not going to add any more posts to this thread. All I can say is that you are headed for a Train Smash.