Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general?

mysql structure as follows:

inventory
    -inventory_id
    -inventory_type_id
    -inventory_name

inventory_type
    -inventory_type_id
    -inventory_type_name

warehouses
    -warehouse_id
    -warehouse_name

aquirings
    -aquirig_id
    -date
    -warehouse_id

aquiring_items
    -id
    -aquiring_id
    -inventory_id
    -quantity

deliveries
    -delivery_id
    -date
    -warehouse_id
    -warehouse_from_id

delivery_items
    -id
    -delivery_id
    -inventory_id
    -quantity

invoices
    -invoice_id
    -date
    -client_id
    -warehouse_id

invoice_items
    -id
    -invoice_id
    -inventory_id
    -quantity
share|improve this question
    
Do you have a specific concern about something with this design? It sounds like you are asking us to design the whole database (or at least confirm your design). –  Max Vernon Nov 14 '13 at 4:29
1  
my specific concern about current design is slow queries while getting stock results when joining sum(quantity) for each document. after following other answers on stackexchange i tried to form my question to comply. and since those questions got positive feedback i thought this was the right place to ask. nevertheless this is my first post so yes i would need some encouragement and directions. –  user2990199 Nov 14 '13 at 8:00
    
@user2990199: You can edit your original post to include your questions and concerns about performance. Also, have you actually tried this? You could create the tables, maybe populate them with a large enough amount of test data (you could write a script to do this), and then measure the performance. Just looking at the design here, I'm not sure I see any immediate problems, although you didn't specify how you plan to index these tables and you didn't give a sample of the query you plan to run. Also what do you mean by "document" as in "...when joining sum(quantity) for each document."? –  FrustratedWithFormsDesigner Nov 14 '13 at 15:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.