Stock Table structure

[SF]

Hi,

I want to setup an inventory dabase and I was not sure what structure to
adopt. Below are two options, hope that someone would advice

Option 1
I just add another typTransactionType that will list all type of transaction
eg Sales,Purchase, Return

tblTransaction
TransactionID Long PK
TransactionDate Date/Time
CustomerID Number
TransactionTypeID Number

tblTransactionDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice


Option 2

I need to set up two tables for Purchase, Sales, Return etc...

tblPurchase
TransactionID Long PK
TransactionDate Date/Time
SupplierID Number

tblPurchaseDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice


tblSale
TransactionID Long PK
TransactionDate Date/Time
CustomerID Number
TransactionTypeID Number

tblSaleDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice

 

[Allen Browne]

Both will work.

If one table (handling both ins and outs) works fine for your field
structure, there are advantages in using that approach.

If the TransactionTypeID is limited to 2 types (ins and outs), you might
consider using the values 1 and -1 and mark it as a Required field. You can
then calculate the stock balance as:
[TransactionTypeId] * [Qty]
If there are other transaction types, it might still be worth including a
TransactionDirection field that is 1, -1, or 0 (for in, out, and records
that don't affect the balance.)

 

[SF]

Dear Allen Browne,

Thank for the advide. I was not aware of the trick (for setting
TransactionTypeID to use 1 and -1) until reading your comment below. I think
this is a useful approach.

SF

Both will work.

If one table (handling both ins and outs) works fine for your field
structure, there are advantages in using that approach.

If the TransactionTypeID is limited to 2 types (ins and outs), you might
consider using the values 1 and -1 and mark it as a Required field. You
can then calculate the stock balance as:
[TransactionTypeId] * [Qty]
If there are other transaction types, it might still be worth including a
TransactionDirection field that is 1, -1, or 0 (for in, out, and records
that don't affect the balance.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to setup an inventory dabase and I was not sure what structure to
adopt. Below are two options, hope that someone would advice

Option 1
I just add another typTransactionType that will list all type of
transaction eg Sales,Purchase, Return

tblTransaction
TransactionID Long PK
TransactionDate Date/Time
CustomerID Number
TransactionTypeID Number

tblTransactionDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice


Option 2

I need to set up two tables for Purchase, Sales, Return etc...

tblPurchase
TransactionID Long PK
TransactionDate Date/Time
SupplierID Number

tblPurchaseDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice


tblSale
TransactionID Long PK
TransactionDate Date/Time
CustomerID Number
TransactionTypeID Number

tblSaleDetails
DetailsID
TransactionID Foreign key
ProductID Number
Qty
Unit
UnitPrice