Business Rules

[KenHigg]

The attached text is from a business rules document I received from a customer after we met for an initial chat. Among other things I explained the concept of business rules and dbs. I showed him an example and he felt confident he could write up the initial draft. We only had to do a couple revisions and I had enough to do the db model... The rest was a breeze...

No new tables for every customer, no new macro to see how much was in stock at the end of the year... (I did store the current stock qty - sorry Pat & Doc)

I believe DOC alluded to a similar step in the norm/denorm thread but referred to it as business requirements.

Mike375, This is where good db design starts (in my opinion). Maybe you will see how this method beats the yeah-but pseudo design method you use.

I would appreciate feed back from others if their method varies a great deal from mine...

???
ken

 

[pbaldy]

I agree the first step is to gather all the available "inputs" and desired "outputs". I try to get (or develop) something similar (that one looks pretty good). I get copies of whatever existing paper documents, Excel files, and reports I can get my hands on. The next step for me would be to lay out on paper (or in Excel) the tables and fields and how they relate to each other. Not until that's all tied down do I open Access and build anything.

While I agree with what you’ve said about new tables for each customer, I won’t pretend to understand Mike’s situation, so I’m staying out of that one.

 

[The_Doc_Man]

Ken, in the final analysis, it is your database. If you store the current stock quantity and it works for you, well and good. I just hope (for your sake, 'cause you seem to be a decent sort) that you never find out the HARD way why storing a dynamic quantity in a static slot is such a bad idea.

The ONLY place where it makes sense (to me) to store the current stock quantity is in the detailed transaction that made the count reach its current value, 'cause that transaction will have the part number & date/time stamp. The stored stock count is valid ONLY for that combination of attributes. (So putting it there, though still not a good idea, at least doesn't violate a normalization rule about data being stored only in a record based on all the keys that value depends on.) You would ONLY be able to access the stock count in that way by finding the FIRST transaction (sorted "descending") for that part number. And if a new transaction comes along later, so be it.

I just think that your method is NOT going to be accurate in the long run. You WILL find some variances eventually. If you need the count to ALWAYS and FOREVER be right, this way ain't the way. The first time that two processes try to write a transaction for the same part number at almost exactly the same time, it is a race that one of them will lose.

 

[monkeytunes]

I've found it necessary to store store stock quantities before to aid in internal audits - I often have to deal with forecasting and procurement/buying departments, and they need to be able to track trends in quantities, warehouse stock, shipping quantities, and suppliers, usually over multiple years. They need to be able to see what we had coming and going in, say, December of 95, December of 96, etc., so they can plan for Decembers to come as the company (and the companies we deal with) change. And December issues are never the same as, say, July issues. I recognize that you can still get this by wading through your forms, text-boxes and formulas...but usually it's just easier to throw it in a table and go from there. (Am I being lazy now, as well as naive? YOU decide!)

Plus due to theft, breakage, mishandling, etc., what we have in stock/on the way does not often match what was actually ordered, what was sent, etc. I can see cases where even a small business might need to store calculated values (probably somewhere AWAY from the normal day to day operations of the application) to be aware of these sorts of issues. I know I'd sure like to know how many, say, lightbulbs break every time I get a shipment in so I can change/keep the same shipping service, or get a month-by-month picture of "dissapearing" items (due to shoplifting, etc.). If the database says "You have 3 kitchen sinks in stock", and there are only TWO on the shelf, well, then the database is simply wrong, despite the correctly calculated value...

All this being said, I'm curious to hear how the pros account for this. I know it's bad practice to store calculated values, and I suppose these "real world" values AREN'T truly calculated, because you'd still have your theoretical calculated value ("this is what we SHOULD have!"), and then your "real world" value which you'd get by actually going and taking inventory with a clipboard and pencil. Then again, maybe you'd opt for recording, say, "broken/stolen/misplaced" items, and letting the "real world" value calculate from there.

But, like I said before, maybe I'm just naive and lazy. That's always possible. (And usually probable.)