Database Design Problen. Multiple transaction affecting one inventory.
Hi guys, I'm a n00b in database design and has been dwelling in a problem for almost a month now.
The problem is like this : There is an inventory that always keep record of a unique item. For example is a motorcycle. Though can be catagorized in several category but all has distinct machine number.
The problem is this. There are so many transaction that involves this inventory.
In the expenditure line there are buying(buying new motorcycle), paying a mediator(if the motorcycle is sold through a mediator), paying a surveyor(if the motorcycle is sold through leasing company).
In the revenue line there are selling(selling motorcycle) and cash back from the leasing company.
All of this transaction can only happen once in the transaction cycle for a particular motorcycle. Paying a mediator, surveyor and cash back are optional but for most case, it happens.
At first I create different transaction detail that connects to one transaction header. But I dont think that is a good idea. But putting all transaction into one transaction detail seems bad either.
Sorry if this kind of problem is already dicussed before. I dont know what key words should I use to find the topic ..
So what should I do? What is the best solution for this kind of problem? Thank you very much and sorry for my bad english ..