I have messed around with my database , but still didnt get good and optimized structure. Currently I did one big table hundreds of duplicated records with it (each line for each event: add, loan, removed, mainten. And some more than once)
Please can anybody give some good advice how to solve this quite simple looking project
I have 1..N warehouses. (scalable)
Each warehouse has products in it (all with same parameters).
There are 2 type of products depend of lifetime.
Best before (starts ticking when added to database)
Usage time in years (starts when product will loaned out to someone).
Products are in groups (2-level products tree Group->Product)
Events that can occur with product:
ADD: set product params + number of product 1..N, time, place, payd_sum, Paym.method, descr of payment. Ps. You can add new product, but you can increase existing product count (new shipment). If you increase number, then every this shipment can have his own lifetime parameters.
MOVE: from one warehouse to other (1..M products)
LOAN: loan out 1..X products to person (time) (persons list is in table t_person)
MAINTENANCE: Any product can be in maintenance: loaned or in warehouse (maintenance start time, description and duration)
END_OF_EXPL.: time, reason (no removing from db table)
My problem is that I cant design properly that when I buy by example 100 new products and put in Warehouse A (with lifetime N). How to store information for that I put 25 products to Warehouse B, 2 products loaned out to person X and 3 to person Y and 10 are in maintenance. And after some time Warehouse B sent some products back to Warehouse A. And after some time added more of same products to warehouse A (with other lifetime). etc etc...
How to save all this kind activity in normalized way? So I can any time ask from database how many items are in Warehous A, B, ..., how many products are in maintenance total and in each warehouse. How many products in which warehouse is over due ( by best before time, by loaned out product lifetime, and maintenance duration ended...) etc etc.
When somebody can help with better and normalized structure I can do other queries to get all those required info.
Sorry, I'm not wery good with databases. I can do php and other programming, but database designing makes some difficulties..