RE: [Web-erp-developers] Re: some serialised work

[Jesse P. <jes...@st...>]

> > serialmoves...hum...Did you check out my response on that=20
> and the sql
> statements in it? you had linked the tables a bit off in your=20
> 1st email
> about them.
> > If you still see problems, let's chat about the=20
> restructuring a bit...
>=20
> Yeah I saw your reply which basically confirmed that we are=20
> into outer joins
> or several copies of the table to get all the movements of=20
> serialised items.
Just Left joins... much faster than Full joins or Right Outers, and the =
SerialItems you start with are indexes... We could force Inner Joins by =
changing the Move Table to record Moves w/o the To/From as you suggested =
and inserting the Initial StockMove movement (which I'd left off since I =
didn't have an initial From move).
=20
> I think knowing:
> - to whom and the date a serial item is sold
> - when a serial number arrived (or was made)
> - when it was returned if it was credited
> This is basically the stock movement inquiry I was talking=20
> about. I think
> this could be a sub-inquiry from the stockmovements inquiry=20
> ie to click on a
> stockmovement record to get the lower level serial moves.
> We also need to know:
> - what serial numbers we have in stock
All possible with the current structure... remember, the SerialItems =
table is also holding the *current* StockMove (not serialmove) the Item =
is associated with - this allows a straight inner join with StockMoves - =
the 'type' of the StockMove tells us whether it is available in =
inventory. It may be better to have SerialItems link to SerialMoves to =
StockMoves, but I'll admit I kludged that w/ code to avoid the extra =
join on another large table.

> Not sure how we get this from StockSerialItems ? I am not happy with a
> requirement to retrieve all stock movements to re-calculate=20
> the stock on hand - a 2 minute query - choking other activity - when =
there=20
> may be none! his also ignores the possibility of purging old data.=20
Theoretically (ahem...bear with me) StockSerialItems should not fall out =
of sync with the StockMoves quantities so long as it is coded correctly =
and people are not mucking with the database incorrectly - those are not =
scenarios I would try to worry about (well, except the coding =
correctly). Really ditto with the StockMoves table... And if you really =
needed to reconcile StockSerialItems against SerialMoves against =
StockMoves (I don't see a need for that ever/often aside from =
development [now]), it may be slow.

> To  my mind this would have to be an update to the SerialStockItems at =
the time of
> a movement.
Currently there kind of is that ... see my note about the kludged =
StockMove above... and the possibilities to unkludge it that I mentioned =
really should alleviate any other non-inner joins.=20
Then again, if we need to account for people purging records without =
some 'approved' purge tool that ensures you keep database integrity, =
alot of things will need to be change to use left joins and a lot of =
extra logic will need to be added to correctly report missing =
records/data.