single or multiple transactions table(s)

[michaelvo]

Hi there,

Some time ago I created an Access DB mainly handling product transactions (order, receipt, in/out warehouse, delivery, etc...).

The DB was designed around a single table for all those different types of transactions. The table is only holding foreign keys.
Apart from the links to attributes (product, supplier, asset, etc..), the table had a field to link it to the action taken (order, receipt, etc..).

> something like :
TRANSACTIONS_TABLE
ID (PK)
productID (FK)
supplierID (FK)
productrefID (FK)
assetID (FK)
etc..
OrderID (FK)
ReceptionID (FK)
DeliveryID (FK)
etc..

(Seperate Order_table, Receipt_table, etc.. have a One-to-Many relationship with this transactions table)

I am not sure this is the best design, partly because only one of the latter fields is not null (it is either an internal order, an Order, a receipt, a delivery, a delivery receipt, etc.. / never 2 or more).

I am starting to completely redesign the DB (and potentially move it to SQL), and would like to do it right this time.

Unless someone has a better idea, I think there are 3 options :
1- keep the design above
2- reduce the "action taken" fields to 2 : one with the ID of the action , and another with the type of action (the FK would then relate to one or another table depending on the type)
3- create a different transactions table for every type of action

Note that I have to query and calculate for ex. the stock (= IN transactions - OUT transactions). So in scenario 3, I will need a Union between the IN & OUT tables before querying...

None of the above scenario seem satisfactory good to me...
Any thoughts or best practice info are welcome.

Thanks,

Michael.

 

[The_Doc_Man]

You have actually already stated the two ways this is usually done, but you missed one little wrinkle.

Case 1 - unified table, with transaction type, other information for stock item, number of items, link to an invoice, etc. Using this style, you would add one more table that you use to validate the transaction type AND include a AddLoss multiplier of +1 if you are adding stock or -1 if you are subtracting stock. Then use a query that joins across the transaction type to let you have a computed field called NetXAct = (NumOfItems * AddLoss). Then one more query to sum the query with the computation and you are done.

Case 2 - a Union query across multiple tables for the different types of transactions in which you supply the transaction add/loss factor as a constant.

SELECT ShippedIn.ItemNum as ItemNum, ShippedIn.Quantity as ItemAmount, .... UNION
SELECT SoldIt.ItemNum as ItemNum, -SoldIt.NumSold as ItemAmount,... UNION
{SELECT for shrinkage table using -Shrikage.NumLost As ItemAmount,...} UNION
{SELECT for returns table use Returns.NumRtnd as ItemAmount,...} etc etc

Then do a summation query on the UNION query and again you have your inventory. Probably should ALWAYS have a date as part of the recordset that contains the raw transactions so that you could do a WHERE clause for transactions before a certain date. That would give you trend-following abilities.

I have seen folks exhorting the virtues of either way. To me, it is immaterial in theory, but your business model might very clearly favor one over the other. That is (and really SHOULD be) a business consideration first and foremost.