Occurrences - Performance question
Several months ago I embarqued on a project to build a fairly complex data base that keeps track of inventory, contract, transactions and invoicing, projections, etc etc etc.
In the begining I must say my knowledge of filemaker pro was very basic and as I moved forward and our company kept seeing more protential I was forced to keep pushing and learning far more complex ideas on my own.
The reason I'm posting today is to get some insight on FilemakerPro's background performance. My issue is that only now have I realized I made a fundomental mistake when I first set up my occurences. Here's a very simple example of the situation I've put myself in.
I have table IN, OUT, INVENTORY
My first goal was to create a tool that could track what's come in and what's gone out and a central page where we could view current inventory. Instead of having an occurence called INVENTORY and 2 unique occurences for the IN and OUT (ie: IN-INVENTORY and OUT-INVENTORY) I directory attched them to the original occurence IN and OUT. From a visual stand point, think a "V" at a 90 degree angle. INVENTORY would be at the point of the "V" and the IN and OUT occurences are running out the sides.
Our company then wanted to add contracts so instead of creating new occurences for the IN and OUT I directly connected the contracts to the IN and OUT that were connected to the INVENTORY occurence further extending the "V" shaped graph. Take that idea and continue it 5-10 fold. Only after the requests became extremely complex did I start to run into an error message saying this occurence could not be related to the same thing twice. I always managed work arounds but never fully understood the reasons why I was getting the error message.
It finally clicked that not only direct relations (one occurence to the next beside it) count but any occurence related to any other linear occurence will be able to see each other.
My concern is that this could be severly hurting the data bases performance and I'm wondering if someone could shed some light on the situation? Generally the data base has been running fairly smooth but I see a slight performance issue since it was first rolled out. The program has only been online for 3 months, records will very quickly pile up and I don't want this to become unusable come the 1 year mark.
Do I have to change all my relationships??