Discussion on transaction storage (accountant and DBA input welcome) (1 Viewer)

[Atomic Shrimp]

In my spare time, I'm designing an inventory and sales system - the goal is for it to be the most perfect system of its kind (no point in aiming low).

I thought it would be good to discuss the storage of transaction data. I want to get this right...

Several different models occur to me:

1. Explicitly-typed documents
Each transaction consists of a header record (in a transaction headers table), detailing the transaction type (Invoice, Credit, GRN, Remittance, etc) and other details pertaining to the whole document.
A transaction details table holds one or more detail records appearing on the document (products, charge items, etc), along with quantities, etc.

2. Mixed type documents
As above, but with the transaction type devolved to detail level - this would allow a single transaction document to be both a credit and re-invoice (in the case of a customer return, say), or an invoice and a remittance (in the case of cash sales), etc.

3. Explicity-typed documents, with an additional batching/grouping layer
As (1), but with another table or data structure, enabling a group of documents to be related together, so that a credit and re-invoice can be later traced as being part of the same customer return and resupply.
(this hybrid solution seems like it might be a bit clunky to administer though).


What do you reckon? None of the above is difficult in terms of creating a data structure, but would any of them be regarded as inherently flawed from an accounting perspective?

 

[gemma-the-husky]

1. Yes, in principle - but you may find there are additonal document types you haven't considered, wihch will affect your table structure. I am not sure I would put them in a common table - most systems seem to have separate tables for each distinct data type. I can't see a good reason to mix items of a different nature, just because they are all transactions, to be honest.

example - of an additonal document type - order call off

customer
order (linked to customer)
orderline (linked to order)
ordercallofff (linked to orderline)

a customer orders 20000 widgets, to be called off flexibly in the future, at 5000 per shipment - an order call-off

and what if there is scope for the price of items to change during the life of the order.


Practical issues like, invoices being paid in multiple instalments, dealing with stock backorders, dealing with stock parts explosions all complicate the analysis. Managing repeated credit notes/debit notes/cancelled credit notes etc are all very complex


2. Yes, but I wouldn't. Its non-relational -
eg invoice/credit it makes it difficult to have multiple credits (eg the price was wrong, AND the shipment quantity was wrong)
eg invoice/remittance - what about soneone who doesnt pay the whole thing in one go -you can end up with 2 different systems, one dealing with account settlements, and one dealing with immediate payments - and then some bright spark lets a cash customer settle at the end of a month! - or requests an advance payment to cover some future deliveries.


3. I wouldn't per se - but batching is very useful. We have already discussed this on another thread -but here's an example

if you have an automatic invoice run that produces 250 invoices, then you have 250 postings to your sales ledger. You don't (trust me, you don't) really want 250 postings to the sales account/sales ledger control account - so - summarize the 250 postings and just post the totals. A good use of batching items.

Of course although we will probably have an invoice header table, we possibly dont need an invoice line table - since the invoice lines are probably represented by indivdiual delivery records. In strict database terms, we probably don't need to sotre all the information we do, within the invoice header (eg an invoice total) since it ought to be computable by reference to the underlying delivery records ... depends how far you want to take it.



does anyone have an invoicing system that DOESNT de-normalize by storing the invoice total.




A perfect inventory system, eh?

offhand, just some areas you may need to consider

multiple costing methods
variance analysis
parts explosions
raising orders for out-of-stock
back order stock monitoring
restocking
minimum stock level monitoring
preferred supplier lists
obsolescence reviews
automated ordering
just in time ordering
consumable stocks
stock taking adjustments
optimised warehouse storage
multiple storage locations
serial numbers
production batch control
waste analysis
stock re-engineering



Hope this gives you some food for thought.

 

[April15Hater]

Just to add on to Gemma's list, you may want to also consider the Lower of Cost or Market valuation method. This will be helpful tax time.

 

[Atomic Shrimp]

Thanks for the comments so far... for the record, I've done this before - designed and built a complete stock and sales system (as well as worked to maintain two others that were designed by my predecessors, and worked in control of yet another two that were commercial packages). None of them were perfect, however.

 

[gemma-the-husky]

you will know what you are doing then

a perfect system is a pretty tall order though

good luck!


I hope you found the comments on transaction storage useful - even if you go another way!

 

[Atomic Shrimp]

you will know what you are doing then

In some respects - I have a background in stock audit and buying, so I'm pretty strong on some bits of stock control, but even then, I've only really worked with things that were finite commodities (not things that were manufactured from parts or raw materials) - there's plenty of food for thought in your suggestions and list.

a perfect system is a pretty tall order though

I know - I am being slightly tongue in cheek about the goal, although, as this is a hobby project (as opposed to the usual "we need a system developed as soon as possible"), it does mean I can invest a bit of time really chewing over the way I approach certain things.

I hope you found the comments on transaction storage useful - even if you go another way!

I did find it useful - thanks. It occurred to me afterwards that all three of my list items could be combined - the batching is something that could happen anyway, and a strictly-typed transaction system could have a type 'multi', or some such - and permit only this type to have multiple, different kinds of transaction lines.

Although that might make audit even harder...

 

[gemma-the-husky]

i think there is a difference between a true relational system and a typical file based accounts system

eg - take purchase invoices - in a typical old style system you enter invoices one at a time. The invoice is stored in the purchase ledge (PL) When you end the input, effectively, the costing information for the whole batch is posted to the Nominal Ledger (General Ledger /NL), in total. Niow there is generally a batch input switch to ask if you want the NL postings to be accumulated totals, or individual items. (typically you would post totals only normal purchases (where a total is sufficient) but detail for expenses - (where the detail is important)

Now also typically, getting back to the original invoice from the NL posting - or finding when you paid the invoice is hard work in this sort of system. You often have to locate a report that showed the invoice postings, because the sytem may not actually retain the purchase analysis entry that generated these postings.

Contrast this with a true RDBS - where all the information remains linked together, and it is much easier to trace everything around the system.

Now strange as it seems, I think smaller company systems are more likely to use a modern system. Larger companies can get trapped into old style file based legacy systems - and getting them to change to a true RDBS is not easy at all - they have to consider expense, reliability, training etc - its often easier for them to carry on with the tried and tested sytem.

 

[Galaxiom]

Invoice costing seems to create some nightmares. There are competing needs to post the invoice to the cost ledgers and maintain coherence with the actual purchase order costs. They get really messed up where the PO bookin is subsequently corrected after sales have been made from it. You really need to be able to carry the effect of the cost update right through the invoices to the cost ledgers.

The invoice line cost is often stored in the invoice where technically it really should be calculated directly from purchace order bookins. However aligning the stock sold with its respective purchase orders is not simple.

If multiple sales of the same product are made simultaneluosly and involve multiple bookins there can be real confusion. I have seen systems go wrong just by changing the quantity on an invoice before it was posted where the change spaned multiple bookins.

Then you must deal with valuing returns and where to put them back into stock.

Unfortunately sales staff and many purchasing officers generally don't grasp enough of the fundamental accounting principles that they really need to be mindful about. I worked on one system where the sales staff routinely oversold out of stock items rather than use special orders and had no comprehension of what this did when the cost of the new stock had changed. In one particular instance a new item costing several thousand dollars was created with the cost left at zero while awaiting the supply and final costing. Meanwhile they invoiced the item as an oversell at zero cost before it was booked in because it was drop shipped.

I tried in vain to get the message across but both salesman and purchasing officer were convinced that nothing mattered other than the stock quantity.

Your system needs to be fool resistant. (Remember there is no such thing as foolproof because fools are so ingenious.)

 

[Atomic Shrimp]

Invoice costing seems to create some nightmares. There are competing needs to post the invoice to the cost ledgers and maintain coherence with the actual purchase order costs. They get really messed up where the PO bookin is subsequently corrected after sales have been made from it. You really need to be able to carry the effect of the cost update right through the invoices to the cost ledgers.

Once a transaction hits the system, it really needs to be set in stone - if it needs correcting, another transaction (or more than one) should be actioned.

And I like my systems to have two date fields for a transaction - 'entered date' and 'effective date' - the latter of which can be set in the past when the transaction is entered - using 'effective date' for calculation of all time-sensitive cost and stock matters.

For example, if a purchase order is booked in at the wrong cost, the transaction should be reversed and booked in again at the right cost - the reversal and the original wrong transaction should then balance out to zero, with only the new, correct transaction being in effect.

It can be a problem if the error is not noticed until after closure of a financial period.

Also, if the stock booked in on the transaction has already been sold (reversing the original transaction would take the stock into negative) - but this can be overcome by entering the corrected transaction first (increasing stock), then entering the reversal of the original wrong one (taking it back down to the correct level).

 

[debmars]

Hi
Ive been reading this interesting thread.

Want some ideas have a look at this program (Invoiceit Pro)

Im into accounting and theres nothing it has not handled.

You get a free trial when you download it. It does Stock control, Backorders, Purchase Orders, Full Accounting. Automatic Transactions, Bank Reconciliation, Batch Invoices etc. You can reverse entries. Find any invoice in seconds. In fact I can find anything Im looking for in less than a minute. In fact everything Ive read in this thread where people are saying its going to be inaccurate to keep track of stock etc. This program keeps track of inventory brilliantly even if one can of beans is 50 cents and another can is 55 cents.


The accounts payable system is brilliant. And reports just awesome

If you want some ideas check it out. I havent seen anyone design a better program in an access format than this one. Its worth a look and will help with your design

So Ive read you have designed material for commercial packages. This guy just designed an invoicing system but it turns out to be a great accounting program. So if he could do that Im sure you could achieve your goal with your database design experience

Just a Suggestion

Cheers
Mars

 

[debmars]

Hi
Ive been reading this interesting thread.

Want some ideas have a look at this program (Invoiceit Pro)

Im into accounting and theres nothing it has not handled.

You get a free trial when you download it. It does Stock control, Backorders, Purchase Orders, Full Accounting. Automatic Transactions, Bank Reconciliation, Batch Invoices etc. You can reverse entries. Find any invoice in seconds. In fact I can find anything Im looking for in less than a minute. In fact everything Ive read in this thread where people are saying its going to be inaccurate to keep track of stock etc. This program keeps track of inventory brilliantly even if one can of beans is 50 cents and another can is 55 cents.


The accounts payable system is brilliant. And reports just awesome

If you want some ideas check it out. I havent seen anyone design a better program in an access format than this one. Its worth a look and will help with your design

So Ive read you have designed material for commercial packages. This guy just designed an invoicing system but it turns out to be a great accounting program. So if he could do that Im sure you could achieve your goal with your database design experience

Just a Suggestion

Cheers
Mars

 

[The_Doc_Man]

One approach that sometimes (not always) helps when transactions have very radically different record-keeping requirements and yet also have to be summed somehow is to try UNION queries that diddle with the signs of the quantity.

Like, when you select a quantity from a table of outflow, the effect on inventory is to reduce the size thereof, so your query takes the negative of the counts from the table. But then, stock adds through a product delivery from your supplier would use the positive form of the number in a separate "leg" of the UNION query. If you get all the legs right, then aggregates of the UNION query give you correct inventory. If the date is included, you can run inventory forwards and backwards to produce charts of sales for a given product or department or whatever, taking returns and shrinkage into account.

 

[Lightwave]

Once a transaction hits the system, it really needs to be set in stone - if it needs correcting, another transaction (or more than one) should be actioned.

Definitely agree with this as it can be absolutely vital to ensure that proper bank reconcilliations can be done.

Can I just ask is it the idea that you yourself will use this system or will you pass it on to others once finished?

It is truly amazing how a user can use a system in a way you completely didn't intend.

 

[Galaxiom]

Interesting to see this thread alive again.

I started another one on the topic of Invoice costing around the same time. In it I argued against the immediate posting of invoice costs to ledgers because the cost of sales can and should be derived from the fundamental data which is the supplier invoice.

While posting of financial transactions certainly must be set in stone the same does not apply to the cost of sales. Despite this the cost of invoice is conventionally posted immediately to a ledger even though the final cost may be different for many diverse reasons.

Usually the cost of an item is recorded in the inventory or related table as a figure derived from the purchase orders. That cost is recorded in the sales invoice. I am arguing that the storage of this figure is a breach of normalisation. It is one of the roughest areas of accounting wth things like cost variance reports used as workarounds.

Instead the cost of an invoice should be calculated at reporting time and then posted. This would then reflect the uptodate information without the complexities of error corrections in either the invoice itself or the input to the system. This allows the bookkeeper to correct purchase order data entry errors and price variations without having to adjust the sales invoice costing.

Once reported the cost must become fixed but leaving reporting to after the supplier invoices arrive will avoid discrepancies generated by unexpected variation in costs against those recorded in the sales invoice.

Any subsequent variations would have to be posted to a inventory cost changes ledger. The system should be able to decide how much of a variation can be passed through to invoices and when it needs to use the variance account.

I don't feel I ever really got my point across about this because others repeated the mantra that the entire ledger needed to be set in stone at the time of the tranasaction.

 

[ions]

John Viescas built a robust inventory app in his building Access applications book. It has good insights and ideas.

 

[TexanInParis]

Thanks for the recommendation, Ions. My sample is a "robust" app, but I must admit that I skirted the issue of costing of incoming inventory by not allowing it to be posted until the vendor invoice is posted. This probably wouldn't work in "real life," as you want the warehouse folks to be able to post the incoming shipment and satisfy any outstanding orders probably long before accounting gets around to doing their thing.