Stock management, simple database solution

Motivation
I decided to create a simple database project related to “stock management”. I mean basic warehouse activities: stock (items) delivery, reservation and release (stock in/stock out). The solution controls amount and value. By “database project” I mean creating database schema, server code (by means of stored procedure) executing basic functionality and unit tests.
I'm also interested if it were possible to create a server code ready to run on DB2 database and SqlAnywhere (also MySql) because the SQL dialect implemented there are very close to each other.

Source code and test code.

Stock management

General idea

• All stock movement is described in terms of “operations”. Operation(document) consists of an  operation header and a list of lines describing an activity related to a single item.
• All documents are immutable. After applying a document the only to way to modify it (even to make a correction) is to submit the next document related to the previous one having a desired correction (plus/minus).
• The solution controls an amount of items, also using fractions (four digits after decimal point). So we can deliver 10 kg. of something and then release 0.5 kg, 0.7 kg, 1.6 kg. etc.
• The solution controls also a value of the items. I'm using a term “value”, not a “price”. By “value” I mean only a value for an accounting purpose. It is usually related to the purchase cost. It has nothing in common with a selling price, it is completely different story. We can purchase an item at price 5$  and the sell it with 10% profit (5.5$), 100% profit (10$), 50% discount (2.5$) or sell it for a song. The purpose is only to have an accounting measure – what is the value of our current warehouse content.
• The document lines not related to any other document line is an item delivery.  It also marks the initial item value. All other operations related to this item (for instance an item release) should contain a pointer to this first line. It is also possible to have a chain of pointers. For instance: 1) “item delivery” - 2) “item release” (points 1) - “correction of item release” (points to 2). The same item can be delivered many times.
• The current content of the warehouse is the item (items) deliveries and a chain of operations related to this delivery. All operations describe change. For instance: 1) deliver 10kg of an item 2) release -2kg of item 3) correct -1kg to this release (one kg more). So the current amount of this item is 7kg (10 -2 -1).
• The solution also manages the item reservation. “Reservation” means that some amount of an item (item delivery) is included in the warehouse value but is not available to release. Of course – this reservation cannot last forever to avoid item zombies. Reservation can be revoked or transformed to an item release. But the logic for controlling the item reservation life cycle (for instance automatically revoking item reservation if a period time elapses) is not developed here, should be the subject of an additional solution.
• The solution also covers item value revaluation (devaluation) to have warehouse value more realistic. It is a type of operation line having zero amount changing but not zero value changing.
• In order to calculate a release value an weighted average is used – just divide value by amount. But it is also possible to set release value manually just omitting this default behaviour.
• All prices and values are fractions with two digits after decimal points.
• Because items can be release in amount of fractions there could be a differences between delivery value and release value. For instance: we deliver 2kg with value 2.01, and then release 2kg with calculated weighed price 1.01 (or 1.00). After that we will get 0 amount and -.01 (or +0.01) value. To avoid such zombie items and keep changes in consistent manner  an additional operation line is generated reflecting only value change. 1) Line (delivery): amount 2.00 value 2.01 2) Line (value change) amount 0.00, value -0.01 3) Line (item release): amount -2.00 value -2.00. By applying this rather artificial solution we keep all history changes consistent without gaps or holes.
• Solution allows multi-store management.  A document header for any item delivery should contain store identifier and all changes related to this delivery modify the item inside this particular store. In order to relocate item between stores an item should be released from the first store and deliver again to the another store.
• Together with the operation lines also “item dashboard” is available. It contains the current item (meaning item delivery) status:  amount, amount available (amount – reservation) and value. This table can be recreated any time by iterating through the operation lines. When the item is released completely the corresponding item entry is deleted from the dashboard. But the item story is left in the operation documents.

Additional information and ideas
In a real business environment this basic solution should be enhanced by a warehouse document management. For instance:  item delivery can be the result of:

• Item purchase
• Correction
• Complaint after selling. The customer returns the item and it is delivered again to the store of “complaint goods”.
• In a factory an item just produced is delivered to the store of “finished goods”.
• Item store relocation.
• During physical inventory some items are discovered and should be included into the database.
• Etc

Any situation like that is described by different documents created in different contexts. But in terms of the warehouse the basic operation is the same: an amount of item is delivered to the store. In the database design it could be an additional table (set of tables) having one-to-one or one-to-many relationship to the basic operation table.
Item description here is very simple – it is enough to have item ID and item CODE. But in a real environment more detailed item description is necessary. But it is not a problem to create a more descriptive table (set of tables) having one-to-one relationship with the basic table.
I believe that it is possible to develop more sophisticated solution just keeping this basic level untouched.
The solution identifies a person. In real environment something more sophisticated is necessary especially related to authorization and safety. Some persons could have a right to access a particular  store, item or are authorized to performed only a specified operation.
Freeze an item for a moment, for instance there is some uncertainty about its quality and we want to stop any activity around it for some time.
End of accounting year.  Move data related to completely released items to archive database. For item partially released move historical data to archive database, close old books and open new books.


Database design.
DDL code.
(Diagram done by IBM Data Studio, freely available here)

The database is a simple star schema, operation and operationlines are fact tables surrounded by dimensions (person, items and stores but more can be added). Itemdashboard is a transactional table containing current warehouse content.

Operation (document header)

CREATE TABLE "OPERATION" (
                "ID" BIGINT NOT NULL, 
                "OPDATE" DATE NOT NULL, 
                "STORE" BIGINT, 
                "PERSON" BIGINT NOT NULL
        );

• ID - primary key, generated
• OPDATE - date of operation
• STORE - store identifier (matters only for item delivery)
• PERSON - person who executes the operation

CREATE TABLE "OPERATIONLINE" (
                "ID" BIGINT NOT NULL , 
                "ITEM" BIGINT NOT NULL, 
                "AMOUNT" DECIMAL(15 , 4),
                "AMOUNTAVAILABLE" DECIMAL (15 , 4), 
                "VALUE" DECIMAL(20 , 2), 
                "REFERENCELINE" BIGINT, 
                "OPERATION" BIGINT NOT NULL,
        "SEQNUMBER" INTEGER NOT NULL
        ) ;

• ID - primary key, generated
• ITEM - item referenced
• AMOUNT - + item amount increase, - item amount decrease
• AMOUNTAVAILABLE -  change in amount available, for reservation only this column should be nonzero  and amount should be 0
• VALUE - change in item value related to AMOUNT. Important: it is value of the whole increase/decrease, no a price of a single unit
• REFERENCELINE - NULL only for item delivery, otherwise change in item delivered, foreign key to OPERATIONLINE
• OPERATION - foreign key to operation
• SQNUMBER - allows keep operation line in order, sequence number of the line in the document

CREATE TABLE "ITEMDASHBOARD" (
     "ITEMID" BIGINT NOT NULL,
     "DELIVERYLINE" BIGINT NOT NULL,
     "CURRENTAMOUNT" DECIMAL(15,4) NOT NULL,
     "CURRENTAVAILABLE" DECIMAL (15,4) NOT NULL,
     "CURRENTVALUE" DECIMAL (20,2)
) ;

• ITEMID - item
• DELIVERYLINE - foreign key to the operation line, item delivery line
• CURRENTAMOUNT - current amount available (increases - decreases)
• CURRENTAVAILABLE - CURRENTAMOUNT minus reservations
• CURRENTVALUE

Temporary table used for probing and operation submitting.

CREATE GLOBAL TEMPORARY TABLE "TEMP_ITEM_CODES_LIST" (
  "SESSIONID" VARCHAR(128) NOT NULL,
  "SEQNUMBER" INTEGER NOT NULL,
  "ITEMCODE" CHAR(30) NOT NULL,
  "AMOUNT" DECIMAL(15 , 4) NOT NULL,
  "VALUE" DECIMAL (20,2)
 ) ON COMMIT PRESERVE ROWS;

• SESSIONID
• SEQNUMBER - sequential number of this line in document. It allows to keep an order of lines.
• ITEMCODE - item code (not item id)
• AMOUNT - amount of item being asked
• VALUE - value of item being asked.

Temporary table used as a parameter for stock operation. Contains lines for performing item changing. Columns in this temporary table reflects columns in the operationline table.

CREATE GLOBAL TEMPORARY TABLE TEMP_ITEM_IDS_LIST (
  "SESSIONID" VARCHAR(128) NOT NULL,
  "SEQNUMBER" INTEGER NOT NULL,
  "ITEMID" BIGINT,
  "OPERATIONLINE" BIGINT,
  "AMOUNT" DECIMAL(15 , 4),
  "AMOUNTRESERVED" DECIMAL (15, 4),  
  "VALUE" DECIMAL(20 , 2) 
 ) ON COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK PRESERVE ROWS;

Server code.
Server code SQL.
The idea is to create API in terms of stored procedure for performing database operation. It is described below. Because more than one item can be delivered (or released) in a single operation the temporary tables (CGTT – Created Global Temporary Tables) are used. Different copy of CGTT is handled for every connection. But in 3-tier environment often the same connection is shared between different users so also SESSIONID column is used for distinguishing data coming from different users.  
One SP is used for item probing (tell if it is possible to release or reserve an amount of item) and the second is an input for performing warehouse operation.
Here is briefly SP description (more details in SQL code comments)

• GET_DELIVERY_ID (UDF) - resolves chain of REFRENCELINE columns to get source delivery line.
• GET_LAST_ID (DF) - DB2 specific. Get last generated identity (autoincrement) column
• LOGMESS (SP) - stored log message for logging purpose (DB2 autonomous block to keep safe against ROLLBACK)
• CLEAR_TEMP_SESSION (SP) - clears temporary (parameter) files.
• STOCK_SIGNAL - signal (exception), DB2 specific implementation
• GET_ITEM_IDS_FOR_DELIVERY (SP) - source file is TEMP_ITEM_CODE_LIST. Output is TEMP_ITEM_IDS_LIST. Resolves item codes and make table ready for item delivery.
• GET_ITEMS_FOR_RESERVATION - source and output as above. It is probing procedure which checks and answers if it is possible to reserve or release item. Resolver item code and amount into delivery lines. Can split one item into several deliveries. If it is not possible returns amount of item available now.
• INNER_OPERATION_OP, OPERATION_OP - OPERATION_OP encloses INNER_OPERATION_OP to issue ROLLBACK command in case of failure. Source file is TEMP_ITEM_IDS_LIST. Applies changes from source file, makes operation document and updates DASHBOARDITEM table. Failure is expected even after running GET_ITEMS_FOR_RESERVATION because between probing (GET_ITEMS_FOR_RESERVATION) and OPERATION_OP another session can release some amount of item.
• UPDATE_DASHBOARDITEM - used by OPERATION_OP and REFRESH_DASHBOARD. Updates ITEMDASHBOARD table.
• REFRESH_DASHBOARD_TABLE - recreates ITEMDASHBOARD table by iterating through OPERATIONLINE.

Transaction
OPERATION_OP issues ROLLBACK in case of failure. But it is responsibility of the caller to send COMMIT command. Item release (operation) can be the part of a broader transaction so all changes can be covered in single transaction.
Safety
All modifications of fact tables (OPERATION and OPERATION line) and ITEMDASHBOARD are made by OPERATION_OP. Also all sql statement are static SQLs. So it is possible to grant for a client application only read-only access to the main tables does making the whole solution more safe.

Testing 
All tests are available here. Two types of tests were executed.

• Functional tests (test1, test2, test3 and test4)
• "Mass" test - look description. Test program (written in Python) creates randomly deliveries and releases of items. This test can be launched from many sessions simultaneously. Every test keeps locally changes expected and before quitting save expected results in TESTSUMMARY table. After test   statement searches for inconsistent entries - should yield 0 rows in case of success.

SQLAnywhere
Current version is available only for DB2 but it is planned also for SQLAnywhere. Because of planned migration I had to resign of several nice features not supported by SQLAnywhere.

• Table, association tables.
• CREATE TYPE - so there are a lot of repeated DECIMAL(20,2) in the SQL and DDL code.
• Condition handler.
• Only FOR loop is used.
• Etc.

Conclusion

This solution is very simple but I believe that it is possible to surround it with more general solution just keeping this basic stock management as separated product and the root for other development.

Because keeping valid stock management is essential I believe that this approach makes sense. Regardless of the quality of the client application we can keep warehouse state consistent and valid for accounting and taxation purpose. All warehouse item changes are documented and consistent.

Future

• XML documents as input or output
• Next layer of software - document handling, marketing, sales
• Sample client application based on this basic solution
• WebService
• SQLAnywehre and mySQL version