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.

I think you should have a batch and an item table and use SUMs. If you index properly performance should be okay.

Giving it a bit more thought the following model would allow you quite a bit of flexibility.