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

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

>So to get the the actual stock movments of a Serialised items Stock Moves we
>have some grotesque, miserable, SLOW SQL summat like ...
>
>SELECT  DISTINCT StockMoves.Type, StockMoves.LocCode, StockMoves.TranDate,
>StockMoves.Qty (we need to know whether the stock was going in or out of
>this location so the sign is important) FROM StockMoves INNER JOIN
>SerailisedMoves ON StockMoves.ID=SerialisedMoves.From INNER JOIN
>SerialisedMoves AS SerialisedMovesAgain ON
>StockMoves.ID=SerialisedMovesAgain.To WHERE
>SerialisedMoves.SerialStockID=$SerialNumberWeAreAfter AND
>SerialisedMovesAgain.SerialStockID=$SerialNumberWeAreAfter
>  
>
select STKMFrom.*, STKMTo.*
from SerialisedItems SI left join SerialisedMoves SM
    on SI.StockItemId = SMGoing.StockItemId
left join StockMoves SMFrom
    on SM.FromStkMoveNo = SMFrom.StkMoveNo
left join StockMoves SMTo
    on SM.ToStkMoveNo = SMTo.StkMoveNo
where SI.SerialNo = '$SerialNumberWeAreAfter'

>StockMoves is already a busy table in its own right and I suspect
>SerialMoves will be substanital too - these inquiries will be S L O W.
>  
>
possibly, but at least it should not affect any other part of the 
system. Usage may show how to better design/revise it.

>Why couldn't the SerialiasedMoves table look like:
>
>SerialMoveID, Primary auto_increment
>StockMoveID,  Foreign key to StockMoves
>SerialID, Foreign key to StockSerialItems
>Qty - I still think we should build this in although I appreciate your only
>interested in 1's
>
>This would be a straight many to 1 with the existing StockMoves table.
>  
>
May be better or at least easier to work with. My thoughts had been that 
you know an item went from A to B, so force it. We can pick up the Qty 
thing from previous discussions that came from the Crediting stuff about 
possibly those things being a type of manufactured product once you're 
ready.

>I am still obviously to get to the bottom of what you are doing, and because
>I don't see it all yet .... I do have some worries :-(
>  
>
Just ask and I'll explain why I did something some way or what I 
intended - it's completely possibly something may be done wrong.