suggest - me not satisfied !

[jason]

I have to keep inventory records. i have designed 3
tables:
items: item_id, name, qty
issued: transaction_id, item_id(FK from items), qty_issued
received:transaction_id, item_id(FK), qty_received

Whenever i receive item...
1.I simply record the transaction as it is to received
table,
2.and add that received quantity into items table (macro
used on onUpdate event).

Likewise i deduct the issued qty from items table while
issuing through issued table. Is my design good or do i
need some improvement?

 

[Steve Schapel]

Jason,

Your design is unnecessarily complicated, and your methods unnecessarily
difficult. For this type of application, you only need one table, with
fields like this...

Item_id
ItemName
TransactionDate
TransactionQuantity
TransactionType (this means issued/received)

When the data is in this structure, data entry is very simple, and it is
really easy at any point to use a query to derive quantity on hand for
any item, totals issued and received, etc.

 

[Steve Schapel]

Jason,

Apologies. On second thoughts, I would do two tables:

Table: Items
Item_id
ItemName
other item-specific fields

Table: Transactions
TransactionID
Item_id
TransactionDate
TransactionQuantity
TransactionType (this means issued/received)