Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    1

    Help with a stock control database

    Hi guys, i m new here and i have a lot of doubts about database design, so i hope you guys can help me out.

    But first of all, sorry for my english, i m brazilian

    Anyway, my problem is the following:

    I m starting to work on an ERP software, and i need to control the product´s stock, but i have a complication, this software is for a medical company, so i need to track each product´s batch number, and that´s where i m stuck at.

    I have already decided some things, like:

    1 - All the products NEED to have a batch number, so an empty batch number will be a batch number called "empty" and not a product without a batch number.
    2 - Each batch number need to have a current available stock quantity that i can easily access.
    3 - I need to track down each batch number "life", something like.. when was the batch number added, to wich customer i sold a product with that batch number.. and so on.

    I have already asked this in a firebird forum, about how to do it, and they gave me a lot of tips, but now my problem is the design itself.

    My first idea was to have a table and store the batch number´s current quantity in there, and have another table with all the movement, but this is going to be very difficult to code and i ll suffer with lock conflits when more than one user try to update the total quantity at same time, and even suffer with uncomitted transactions so i think using it is out of question.

    Then started thinking about using just one table to store all the data and use a view and SUM() to calc the current stock quantity, but i think this will become very very slow as the table´s records begin to grow.


    So if anyone here have any kind of experience or any ideas about it, please help me out.

    Any help is welcome!

    Thanx in advance,

    Fábio.

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I think you should have a batch and an item table and use SUMs. If you index properly performance should be okay.

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Giving it a bit more thought the following model would allow you quite a bit of flexibility.
    Attached Thumbnails Attached Thumbnails batch.JPG  

Posting Permissions

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