Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2016
    Posts
    10

    Smile Time-based entries/queries

    Hello,

    I have a design related question for time entries. This question is independent of a particular database. Mostly just the concept itself...

    Let's say for example that I have an inventory table with a SKU column, PRICE column, and QUANTITY column.
    The PRICE column and QUANTITY column will constantly change values and I would want to be able to query a graph of those values over time for each SKU.

    I thought about creating another table with the time-stamps of all changes, but it seems to me inefficient for queries because the table could be very big with multiple changes every day on a lot of material. I'm also wondering if there is a database system that logs the history of each change in value. There will also be a lot more values changing that need to be queried, not just these three.

    I am completely new to the world of databases, I'm trying to implement a system for a small business and I'm wondering where to go from here in terms of design.

    Main Questions:
    1. How would most people setup this particular example?
    2. Do time-based databases exist that log historical change?

    Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Most designs would not store an actual value (quantity) for stock.
    Normally you apply transactions and then derive the stock on hand.
    Iv seen desugns tgat gave periodic stockcounts to tie in with financial auditing or just better operational stock.
    In principle they are all the same technique
    All sum transactions in a specific period
    If you do stock counts run thd transactions from the last count.
    If you needto know stock at a specific point in time sum transactiobs upto that time.
    You need to model what transactions are ie those that add to stock (supplier deliveries, customer returns, production), things that detract ftom stock customer sales, supplier returns, qc reject, wastage, shrinkage etc...
    In the oast ive modelled that as a transaction code with a description and effect. Effect is +1 or -1 if plus it adds to stock, otherwise subtracts from stock multiply the effect by quantity in the transaction gives the stock movement,
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by healdem View Post
    Most designs would not store an actual value (quantity) for stock.
    Normally you apply transactions and then derive the stock on hand.
    Iv seen desugns tgat gave periodic stockcounts to tie in with financial auditing or just better operational stock.
    In principle they are all the same technique
    All sum transactions in a specific period
    If you do stock counts run thd transactions from the last count.
    If you needto know stock at a specific point in time sum transactiobs upto that time.
    You need to model what transactions are ie those that add to stock (supplier deliveries, customer returns, production), things that detract ftom stock customer sales, supplier returns, qc reject, wastage, shrinkage etc...
    In the oast ive modelled that as a transaction code with a description and effect. Effect is +1 or -1 if plus it adds to stock, otherwise subtracts from stock multiply the effect by quantity in the transaction gives the stock movement,

    I think I understand what you're saying...

    You are saying that if I have, let's say 10 quantity of the same SKU, to implement 10 rows of the same SKU with 10 static data sets rather than a single variable quantity? And then tie the time-stamp or location data to each 10 single entry. Is this correct?

    The reason I thought of variable quantity is because I'm using "Smartsheet" for a very small inventory where I'm inputting information manually through web-forms and I don't think it can take all these static entries, there would be too many. It would be easier to update just the quantity. The inventory is small, but lots of fluctuations in quantity. I would be using this mostly for quantity/cost metric analysis over time. I would like to tie it to the accounting system only in the future when the inventory grows. I don't know if such a thing exists with minimal coding required.

    Suggestions on software that can do this would be great. Thanks
    Last edited by Lex1182; 07-10-16 at 17:36.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    You are saying that if I have, let's say 10 quantity of the same SKU, to implement 10 rows of the same SKU with 10 static data sets rather than a single variable quantity? And then tie the time-stamp or location data to each 10 single entry. Is this correct?
    nope Im suggesting a table for
    products
    transaction types (describe what the transaction is and how it affects stock
    transactions (call'em stock movements if you prefer)

    storign that way, assuming there is need for it gives a more accurate visibility of the stock position (IE when a delivery occurs, what is the wastage and so on. but its not needed for every situation.

    if your prefer to store a stock AND you need to know what the stock has been over time then you need to store the stocklevel and a datetime that stock level was valid

    essentially thats a table for products and a table for stocklevel
    the stocklevel table wouldl have the date and time of the stockcount, the product ID and quantity. the date and time of the count + the productid would be the PK (probably as ProdcutId first then date and time, but do your own modelling to prove if that is right)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by healdem View Post
    essentially thats a table for products and a table for stocklevel
    the stocklevel table wouldl have the date and time of the stockcount, the product ID and quantity. the date and time of the count + the productid would be the PK (probably as ProdcutId first then date and time, but do your own modelling to prove if that is right)

    OK, I understand. Two tables it is then...
    SKU will be the productid with PK
    And the stocklevel table will have the timestamps over time with the modifier in quantity.
    I think the modifier in quantity will have to be a static quantity value for each timestamp instead of variable +1 / -1 as you suggested previously, because base quantity will always fluctuate.
    But i'm starting to understand what you mean.

    Thanks for your help!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    the +1/-1 only applies if you
    a) have a transaction table reflecting each and every movement of stock
    AND
    b) choose to use my preferred design of a transaction code which has requires an identification of how it affects stock. Ive seen designs that use effectivley double entry bookeeping on stoick
    Ive seen designs that store the direct affect on stock (ie if a sales to a customer then the transaction records this as -xxx units

    what you are, I think, wanting to do is store the actual stock level at a specific instance in time. Its probably a smarter move performance wise as you dont' have to roll up all the transactionm since the last opening or closing balance, but you do lose the true stock movement as all you have is the stock at a point in time as opposed to the ebb and flow of stock levels.

    your way means you only know the stock at a specific already recorded point in time (the date and time you enter into the table. a transaction table doesn't care what about that, it knows each stock movement against a point in time. So say operationally the organisation deciedes it retrospectively wants to know the stock on opening, at midday, and mid afternoon.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by healdem View Post
    the +1/-1 only applies if you
    what you are, I think, wanting to do is store the actual stock level at a specific instance in time. Its probably a smarter move performance wise as you dont' have to roll up all the transactionm since the last opening or closing balance, but you do lose the true stock movement as all you have is the stock at a point in time as opposed to the ebb and flow of stock levels.

    your way means you only know the stock at a specific already recorded point in time (the date and time you enter into the table. a transaction table doesn't care what about that, it knows each stock movement against a point in time. So say operationally the organisation deciedes it retrospectively wants to know the stock on opening, at midday, and mid afternoon.

    If the stock at opening, midday and mid afternoon can be derived from the closest time-stamp right before those times and read the static quantity off of that row, then I can also see do the ebb and flow of stock levels through another static column in the same table., no?

    Double-entry also seems interesting though... I'll have to think about it. It has the potential for a future-proof scaling into a more complex accounting system.
    Last edited by Lex1182; 07-11-16 at 14:30.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    on your current approach, IE storing a stock level against a specific point in time, you only know the stock at that point in time. by storing stock movements you can derive the stock position at any time or any interval as required.
    your approach knows the stock level at say 08:00, 12:00 and say 17:00, but it doesn't "know" what the stock postiion was at say 14:00, or arguably 11:59 and / or 12:01. Now if there are no stock movememnts that happende around 12:00 thats not an issue, but you cannot infer that the stock at 14:00 was the same as the stock at 12:00. it may be but you have no way of knowing. if you decide to do some forecasting of stock your approach has no knowledge of anythign other than at a cedrtain point in time the stock is blah., but blah may well be the same as the previous stock. say your stock is 34 at 08:00, you get a delivery of 1234 units at 09:15, 1030 sold at 10:21, 204 sold at 10:30, the stock level at 12:00 is still 34 but your approach has no knowledge of the movememnts. now if thats not a requirement for your system then fine, the KISS principle has a lot to be said for it
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by healdem View Post
    on your current approach, IE storing a stock level against a specific point in time, you only know the stock at that point in time. by storing stock movements you can derive the stock position at any time or any interval as required.
    your approach knows the stock level at say 08:00, 12:00 and say 17:00, but it doesn't "know" what the stock postiion was at say 14:00, or arguably 11:59 and / or 12:01. Now if there are no stock movememnts that happende around 12:00 thats not an issue, but you cannot infer that the stock at 14:00 was the same as the stock at 12:00. it may be but you have no way of knowing. if you decide to do some forecasting of stock your approach has no knowledge of anythign other than at a cedrtain point in time the stock is blah., but blah may well be the same as the previous stock. say your stock is 34 at 08:00, you get a delivery of 1234 units at 09:15, 1030 sold at 10:21, 204 sold at 10:30, the stock level at 12:00 is still 34 but your approach has no knowledge of the movememnts. now if thats not a requirement for your system then fine, the KISS principle has a lot to be said for it
    Thanks buddy for taking the time to elaborate, but I can know the movement by adding a location in addition to a quantity for each timestamp. As I was saying earlier " through another static column in the same table " I can add the location of the item.

    But as you said, if I don't have locations I should be fine. You put me in the right direction with the two different table approach. And I might possibly look into double-entry.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Sorry I disagree
    If all you have is a stock level at a specific point in time thats all you know. The level then was x. There is no knowledge of how that level was generated, unless say it can inly change at the same specified interval. Even then you will not know that say the kevel rdmains static, but in that time googs have been received, goods have been sold and anything else that affects stock
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by healdem View Post
    Sorry I disagree
    There is no knowledge of how that level was generated
    I think I understand what you're saying. So let's say, you have 10 cans of soda in a store as inventory and then 2 cans are deducted to make 8 left as total. 1 can is scanned at the machine, but the other can was used as a free demo to client. Then, you have 8 left but you don't know how the other 2 were deducted and how they flowed, which is also bad for GAAP accounting standards and really bad in the context of collecting analytics and metrics.
    Last edited by Lex1182; 07-11-16 at 15:46.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    No
    Scenario 1
    Opening stock 08:00 is 10
    2 are disposed of (whether thats sold, given away, damaged...) closing stock 8 at 12:00

    Scenario 2
    Opening stock 08:00 is 10
    09:15 24 delivered, stock 34
    09:30 10 sold,stock 24
    10:00 24 delivered, stock 48
    10:02 16 sold, stock 32
    10:30 16 sold, stock 16
    11:45 6 sold, stock 10
    11:50 2 damaged, stock 8
    12:00 8 in stock

    Both s enarios have the same opening and closing level, in your model both scenarios would look the same.
    But if say you wanted the stock for 10:00 you cant get to it
    Your model gives no indication of activity. Your only warning of stockout is consistently low levels. But a slow moving item may well be low stock. A faster selling item should gave higher stock.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by healdem View Post
    No
    Scenario 1
    Opening stock 08:00 is 10
    2 are disposed of (whether thats sold, given away, damaged...) closing stock 8 at 12:00

    Scenario 2
    Opening stock 08:00 is 10
    09:15 24 delivered, stock 34
    09:30 10 sold,stock 24
    10:00 24 delivered, stock 48
    10:02 16 sold, stock 32
    10:30 16 sold, stock 16
    11:45 6 sold, stock 10
    11:50 2 damaged, stock 8
    12:00 8 in stock

    Both s enarios have the same opening and closing level, in your model both scenarios would look the same.
    But if say you wanted the stock for 10:00 you cant get to it
    Your model gives no indication of activity. Your only warning of stockout is consistently low levels. But a slow moving item may well be low stock. A faster selling item should gave higher stock.
    AHHHHHHH!!!! Here is the misunderstanding.... That's because I mentioned that it's a small inventory with manual stock entry. In other words, the webapp will have operations to subtract or add from the existing total while still maintaining relative "TIMESTAMPS". I mentioned the word timestamp many times to denote the inbetween time with no open or closing stock. I also mentioned the words manual entry many times to denote the small inventory and static values generated by the webapp for data entry, but maybe I wasn't clear. I also mentioned that since there is a webapp involved, i will do minimal coding, there is a need there for the form to do most of the work since no database design is required in that case. Here is the example bellow:

    Scenario (no opening and closing or operations in the table)
    09:15 stock 34
    09:30 stock 24
    10:00 stock 48
    10:02 stock 32
    10:30 stock 16
    11:45 stock 10
    11:50 stock 8

    (this above is the second table you suggested with the "timestamps" and SKU info in another table link through PK)

    In my way of thinking there is no need for open and closing stock because there is an operation that takes the closest timestamp right before the request time of report. The operation of the form will do the following operation {past stock - sold stock} = current stock or {past stock + new stock} = current stock or {open stock = latest date before x/x/x }. And each dump of "current stock" will have a timestamp with location. I was thinking that is allows me to migrate systems more easily this way if all the information is static with fewer tables. I'm seeing the database more as a repository and let the forms handle the operations. Obviously I would do two tables as you suggested and you helped clarify that aspect which was my original question. But then you insisted on this other thing... Is there a reason why it wouldn't be good to completely omit opening/closing and stock operations from the entries in the database?

    I did mentioned time-based in the title of this thread because it will be used for analytics of competitive markets that have lots of fluctuating prices over time, not as much for accounting (Although I wouldn't want to be boxed either for the future).

    You reply should target what I wrote in bold please and location issue doesn't apply in this case. I can add that.
    Last edited by Lex1182; 07-11-16 at 18:42.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Im not ibsusting on anything, its not my application, its not my design. You do what you think meets the business requirement.

    You asked
    1. How would most people setup this particular example?
    2. Do time-based databases exist that log historical change?
    Most people would yse a transactions table approach not store current stock UNLESS it was a very low volume db where there is absolutely no risk of db locks degrading system performance
    Personally Ive never come accross the term 'tine based databases'
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by healdem View Post
    Im not ibsusting on anything, its not my application, its not my design. You do what you think meets the business requirement.

    You asked
    Most people would yse a transactions table approach not store current stock UNLESS it was a very low volume db where there is absolutely no risk of db locks degrading system performance
    Personally Ive never come accross the term 'tine based databases'
    I appreciate your help, I really do!!! Now that you told me how most people would do it, I guess i'm just trying to understand how my method would not yield the same result? I'm not an expert, just trying to understand why you disagreed earlier when you said it would yield a different result about the "location" info when all I have to do is add a location to the table?

    The term time-based is strictly of my own making for denoting a database that will store emphasis on action-based information. Actions usually have an association of time/location. And I wanted to know if there is such a database with minimal coding required that has emphasis on this information but that would also scale into an accounting system later-on.

    I'll re-iterate my questions:
    1. What are the practical disadvantages of omitting operations and open/close times in the stock level?
    2. Is it ok to have such a huge table with timestamps that never end?
    2. Is there a database software that requires minimal coding that will emphasize on time/location data and scale into an accounting software?

    Thank you in advance.
    Last edited by Lex1182; 07-12-16 at 12:32.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •