Inventory Database Design

[Dal Jeanis]

Hi Rain - I apologize if it seemed I was ignoring you. I see that my reply up in 23 could have seemed curt, and that's sometimes a problem for me. In the first version of #29 I had written a compliment to you about your sample database, and a question about other ways of doing the company selection, then I lost the long post that I had written. I was still rewriting it when you wrote your post 28, so I hadn't had a chance to review your post. I've modified my #29 to make that clear.



COMBOBOX

I had forgotten the NotInList event, and I was thinking to do the unbound combo box, and after update, detect and handle the addition. Whether I needed to set a bound and hidden field to the value desired, or use the unbound combobox as a master-subform linkage field would depend on the architecture of the form. Clearly, using the inbuilt event is a better architecture, whether the combobox is bound or not. I've modified my answer 29 to incorporate that event.

NEGATIVE VALUES

If the Entity described by a merged Transaction represents outgoing parts, then in my mind the quantity is inherently negative. Coming from an accounting background, I don't have any problem with that, and it's a quite natural way for me to look at it. Some other people might instead say . Thus, my statements:

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,

UNIONS

I did express my preference for a single table, but I also see how the three-table solution does simplify certain design elements for the guy who has to maintain it. (And it eliminates the negative sign from the outgoing Transaction table, although if the Adjustment transactions can ever add units, there may be a need for recording a plus/minus sign on that one.)

Also if you need to use Union Queries then again your design is wrong.

That's an intriguing statement. If you have three different transaction tables, like in this database, and want to report all the transaction data for a single Part Number, how do you report that detail without a UNION?

CUSTOMER ID ON MULTIPLE RECORDS

Rain, did you have any opinion on the question of duplicating the customerID down to the Part table? Since different customers can have the same Part Number, I don't see any way around it, despite it violating a basic integrity rule. I'd almost duplicate the CustomerID to ALL the relevant tables, which would simplify data access and reporting in various places.

CORRECTIONS IN GENERAL

Please, please, please feel free to correct me whenever I say anything that comes across as inaccurate - I make mistakes like everyone else, and while I prefer to learn from other people's mistakes, I like to learn from my own, too. It doesn't help anyone if erroneous or misleading statements go unchallenged.

[Rainlover]

Dal and Dycon,

I would like to say that between you both you have come up with a good design. Not perfect but good.

The first thing I would comment on is tour use of EmployeeID. Other than you are breaking Normalisation Rules I can't see why you need it. But this is a personal opinion. I have found that people share logins so it ends up inaccurate. If you then use it to pin point a person's mistake you could be blaming the wrong person which means you end up with egg on your face. As I said a personal preference. I have used this on every table together with a TimeStamp. Got into lot of trouble because I was chasing negative behaviour rather than positive. Leave it alone if you wish.

This one is important and discussing Acquisitions which goes into stock. . tblPartPO should have the Name of the supplier. A separate Table would be required to list all suppliers. Then under that you would have tblPartinfo. This would also be the design of your form. Purchase Order in the main Form and Parts in the sub form. So you have a supplier on the Main and a list of parts you are purchasing in the Sub Form. Everything in tblPOInfo could be included in the Purchase Order Table. The Purchase Order table would select the parts from tblPartInfo.

Next we need to look at Sales. The same design would be applicable here except you are dealing with Customers instead of Suppliers.

Now lets see if we can clear up a few points that you raised. I would think that with the slight alteration to your design as I suggested would overcome them.

I don't see anything that requires clarification except Union queries. Your example of a report on all transactions for a particular part would be an unlikely question. But if it were asked I would report on Purchases, Sales and adjustments separately. The one report with three sub reports. Having everything mixed together would be a nightmare to decipher.

If there is anything else then simply post a question here.

I have not read the entire thread but from what I have read then the design should suffice.

If you were dealing with things like buying several part to make one part for sale then this would require further consideration. However with these basics you should be able to add things on if required. Just one more thing. This is not an accounting package. Suggest you stay clear of trying to calculate profits etc. Allow your accounting package to do that.

Good luck to both of you. You are working well together.

[dylcon]

Rain,

1) For the EmployeeID, I do not understand how this will cause problems. It seems like it is a pretty straightforward way of doing things. I will not have user logins to the database, and generally, it will be one person doing 90% of the data entry.

2) The most specific filter I will be using is PART NUMBER strictly. Most of the time, the PO number will not even matter. It is just being used to track dates essentially and it is a good piece of information when seeing when it was shipped, and along with which other parts, if any. That being said, do you stand by your statement that my setup should change?

3) I am ignoring sales completely. This is not meant to handle sales at all. This is simply a simple way for us, my company and my company only, to handle our inventory on a daily basis. This is the same for accounting.

[Rainlover]

Keep working with Dal.

Or do you have a particular problem you need help with.

[dylcon]

Was that response before I edited my post? If not, thank you for all of your help, Rain

[Rainlover]

Rain,

1) For the EmployeeID, I do not understand how this will cause problems. It seems like it is a pretty straightforward way of doing things. I will not have user logins to the database, and generally, it will be one person doing 90% of the data entry.

Totally up to you. I just don't see the need. It certainly should not cause any problems.

2) The most specific filter I will be using is PART NUMBER strictly. Most of the time, the PO number will not even matter. It is just being used to track dates essentially and it is a good piece of information when seeing when it was shipped, and along with which other parts, if any. That being said, do you stand by your statement that my setup should change?

I most certainly do. I believe you should separate the ins from the outs. You already have the Purchases, just duplicate that for your sales in order to keep them separate.

3) I am ignoring sales completely. This is not meant to handle sales at all. This is simply a simple way for us, my company and my company only, to handle our inventory on a daily basis. This is the same for accounting.

I don't understand this one as you have a Table set up for this purpose. Besides if you don't handle the outgoing parts how will you know what you have in stock.

PS I did say somewhere recently that I have not read every post completely.

[dylcon]

I most certainly do. I believe you should separate the ins from the outs. You already have the Purchases, just duplicate that for your sales in order to keep them separate.

I am a little confused as to what exactly you mean by this. What are the ins and outs that you are reffering to? What purchases do I have set up? Which relationship capture were you looking at? I am probably more or less confused about jargon, seeing as I am quite the Layman in this realm.
My outgoing table is not exactly sales though. Our supplier and customer are the same all of the time because we provide more of a service than we do an actual product.

[Rainlover]

I am looking at Post Number 27. The tables on the right hand side. Sorry I did not enlarge on this better. I treated them as purchases. Your purchases from another supplier.

Then I suggested that you duplicate them (With different names) for what the customers purchase from you.

My outgoing table is not exactly sales though. Our supplier and customer are the same all of the time because we provide more of a service than we do an actual product.

This sounds like you buy from supplier and sell to the same supplier. I know this can't be correct, so could you please explain.

[dylcon]

Parts are sent into us, we alter the part in some way (metallizing, paint, or pad print) and send them back to the supplier. Sometimes we recieve and ship to different companies, but not very often.

[Dal Jeanis]

And, once again, by the time I hit enter, the conversation has gone beyond where it was when I started my long post...

Rainlover - I concur that the design is good, not perfect.

BACKGROUND - As I understood it, dylcon works for a jobber. The inventory that dylcon is tracking isn't really "inventory", it is parts belonging to an originator that have been sent to the jobber to be worked with one of three types of surface/finish processing ('metallize', 'pad print', or 'paint'), and then be shipped back to their originator. There isn't a supplier distinct from the purchaser, just a single client company for each part, who actually owns the parts being processed. That distinction between the business processes is why I didn't suggest dylcon adopting your general design, just using it to inform his own design.

That distinction also means that the "flow" of the client's parts through dylcon's plant is an important part of the knowledge that the database will provide, and dylcon's going to want a report, by part, sorted by date, and based on a union query of all six types of transactions. It's not difficult, and it is a business need that provides valuable information.

dylcon - If you read Rainlover's posts assuming he didn't know that part of your operation, what he said makes perfect sense.

A) You are already separating the three types of transactions (in/out/adjustment), which Rainlover suggested as a best practice.

B) Rainlover provided feedback that he has gotten in trouble regarding certain expected usages of the employee ID data, and thus would leave it out in a future design. Just understand his experience and apply it appropriately to the political situation at your work. It may not be relevant, but if it was, he just saved you from a "career limiting move".

[dylcon]

Thank you so much for allowing me to comprehend exactly what you are always talking about Dal, I sincerely appreciate how much help you have been.

Unless, you think that I should be linking the incoming,outgoing,adjustment tables to both the part and the PO? That is the one question that has not been entirely clear to me yet.

Another thing is that when I am entering in a new part#, I need to relate it to a customer. How will this be done? This is why I originally had customer attached to the PartInfo and not the POInfo. What exactly should I do about this? It is scaring me

[Dal Jeanis]

tblPartPO provides transactions with a unique link to both, so you have the link you were asking about.

The only remaining glitch is the likelihood that some day, more than one company will give you the same part number. That condition argues that a CustomerID on the tblPartInfo table is obligatory, even though it's generally considered poor design. Another, different type of poor design suggests you could add the customer id on to the front or end of the part number to make it unique. Don't do that.

My choice would be to bite the bullet and add the customerID onto tblPartInfo, with a unique index on customerID/PartNumber. It avoids more problems than it causes. You just have to be aware that the presence or absence of PartInfo and POInfo records may affect query results, based on how the query is coded. If you train yourself to always code your queries from left to right (therefore many to one) in the diagram that you put up in #27, you shouldn't ever encounter that problem.

(In a weak moment, I considered propagating the CustomerID to every table other than tblPartType, tblAdjustmentType and tblEmployee, and you should hold that in your back pocket just in case you run into something you totally need to do but can't. It would simplify lots of queries.)

[dylcon]

My choice would be to bite the bullet and add the customerID onto tblPartInfo, with a unique index on customerID/PartNumber.

What is a unique index on customerID/PartNumber?

[Dal Jeanis]

I meant a composite index using multiple fields, with duplicates not allowed. So, you can have a record for (Co=1 and Part = ABC123) and one for (Co=1 and Part = ABC456) and one for (Co=2 and Part = ABC123), but you can't have two records for (Co=1 and Part = ABC123).

http://support.microsoft.com/kb/304272

http://en.allexperts.com/q/Using-MS-...alue-check.htm

[Rainlover]

And, once again, by the time I hit enter, the conversation has gone beyond where it was when I started my long post...

Maybe you are covering too much in one post.

BACKGROUND - As I understood it, dylcon works for a jobber. The inventory that dylcon is tracking isn't really "inventory", it is parts belonging to an originator that have been sent to the jobber to be worked with one of three types of surface/finish processing ('metallize', 'pad print', or 'paint'), and then be shipped back to their originator. There isn't a supplier distinct from the purchaser, just a single client company for each part, who actually owns the parts being processed. That distinction between the business processes is why I didn't suggest dylcon adopting your general design, just using it to inform his own design.

That distinction also means that the "flow" of the client's parts through dylcon's plant is an important part of the knowledge that the database will provide, and dylcon's going to want a report, by part, sorted by date, and based on a union query of all six types of transactions. It's not difficult, and it is a business need that provides valuable information.

dylcon - If you read Rainlover's posts assuming he didn't know that part of your operation, what he said makes perfect sense.

I did not realise that this was the case. Inventory carries with it a particular concept. This database does not really fit the word inventory. At least as far as I am concerned. My advice has been based upon a wrong understanding. I have even gone back and reread the entire thread and still did not arrive at the proper conclusion. At least now I know.

B) Rainlover provided feedback that he has gotten in trouble regarding certain expected usages of the employee ID data, and thus would leave it out in a future design. Just understand his experience and apply it appropriately to the political situation at your work. It may not be relevant, but if it was, he just saved you from a "career limiting move".

This is not overly important. If it causes problems it can be removed without much difficulty.

Cheers