Table design - invoice with inventory serial number (OpenInsight 32-bit)
At 10 MAY 2012 05:50:20AM So yet Alexander Lim wrote:
I am trying to do a form - "invoice" with every stock item can have multiple of serial number base on quantity.
Example:
stock item 1 , sell 10 pcs, 10 serial number.
This is easy in table design - just add a new column and set it multivalue. (:biggrin: another limitation here, one edit table cell can only have 9999 characters)
The question here is :-
I need to search the serial number belong to which invoice. This can be solve using Rlist or select statement.
But using Rlist or select statement have speed problem ( when data grow ).
Speed is crucial here, where i need to validate the 'serial number' is unique and must purchase in then only can sell…etc
By Adding index to this "serial number" column will it solve the speed problem? i have bad experience with index, most of my customer index file is corrupted quite frequently.
Is there other workaround? or my design is failed :sad:
At 10 MAY 2012 06:15AM Andrew McAuley wrote:
There is no reason why a BTREE index would not work but in your case it might be simpler to use a relational index. Then a single disk read will establish uniqueness.
World leaders in all things RevSoft
At 10 MAY 2012 10:19PM So yet Alexander Lim wrote:
i think all the index option can't solve my problem.
Because one or more stock might have same serial number.
Then i need to create a new table , "Stock_code" as key 1, "Serial_number" as key 2.
Then i code a subroutine ( self coded index ) to save the detail like invoice_no, invoice_date, etc…
When i need to know the available serial number for a stock, i must use Rlist on this new created table. Which i afraid it will have the speed problem again.
Please correct me if i am wrong. Thanks
At 11 MAY 2012 04:06AM Andrew McAuley wrote:
You are wrong :). You are thinking like a SQL programmer. OI relies on row based transactions not set based. What you have described is a relational index. A primary key of Serial number and an MV field containing stock codes. A single read will establish all stock codes sharing a serial number. No joins required.
World leaders in all things RevSoft