Hi,
I need to design a table header for inventory transactions with the specifications as follows:
1. System-generated series numbers (integer)
2. Series numbers must be unique by branch by transaction type. Thus if I have following:
Branches: Br1, Br2
Transaction Type: SRS (Stock Receipt from Supplier), SRB(.. from Branch)
The series number must be implemented in such a way that,
Br1 SRS 0000000001
Br1 SRB 0000000001
Br2 SRS 0000000001
Br2 SRB 0000000001
Then, in every INSERT, series number should be incremented by 1, grouped by branch by transaction type. That is, after INSERT with Br1/SRB the figure may now look like,
Br1 SRS 0000000001
Br1 SRB 0000000002
How do I design my table in order to achieve this? Note that this table header will have a detail (master/detail) referenced by foreign key.
Thanks in advance.
What I have come up so far are the following:
1. Create an identity field which will be the designated PK for the table header.
2. Branch, Trx_Type, SeriesNum will be a compounded index with a unique constraint.
3. In the branch office, there will be a shared text file containing the last series number for the branch so that, upon saving the transaction (setup is real-time online), the system will get the last series number from the file then increment by 1 and use it as the series number for the transaction. Upon completion, the system will update the file with the new last series number.
I need your comments on this, and if you have a better solution, pls let me know.
No comments:
Post a Comment