Suggestions for my data base 2.0
I currently have a somewhat well working data base. When I first started building it, my knowledge of FileMaker Pro was minimal but over time and with the help of this wonderful forum (mostly Phil, not discredit the help of others though!) I was able to build a fairly strong system for our company.
That being said, I feel it is now time to create a 2.0 version of my current database to increase it's performance/longevity and remove all the mistakes I made in the early days.
So I'd like to start this thread to get some food for thought before I dig into the somewhat monstrous task and toss out a few ideas to see if I will be setting myself down the right path this time. Most of these ideas come from things mentioned in passing on other threads I've posted and instead of digging up the old ones I'd like to bring them up here and have them all under the same roof.
I think the biggest issue with the current database is the lack of archiving records. I have two main example that demonstrate why it's becoming more and more important to have this ability to increase the data bases performance. Currently our inventory must calculate thousands upon thousands of purchase and sales records dating back to day 1 to tell us what we have on hand. This is achieved by having a ledger table between the purchase and sales tables calling upon the products purchased and sold and calculating the difference. Loading this page can take sometime but is still performing relatively well. Second, we have a table that we use to forecast our sales and purchases based on our current contracts. This one was a little trickier to build and was without question macgyvered to make work with the knowledge and design I had to work with at the time. The biggest kicker in this forecasting tool was that our buyers needed to see, in the same window, a list of all the purchase and sales contract in order that they were created and the ability to get averaged prices based on the selected number of records to "go back on" my solution was somewhat messy, with script triggers creating new records in this table that copied over some information and have some calculation fields pulling over other information and this has become terribly cumbersome in it's performance. I had no choice but to do this because the purchase and sales contracts are in two separate tables. My solution (please feel free to suggest other ideas) would be to implement archiving and consolidating purchase and sales contract into one table allowing for easy listing and completely eliminating the scripts that copied over all the information. I've also played with the idea of doing the same for the individual purchase and sales transactions (not sure about that one)
Phil if you're reading this, I know at one point way back in the day you mentioned that you have a nightly script that runs and archives all of the days invoices copying over all the pertinent information (like amount of parts sold etc etc.) into master records for each inventory item that acts as a ledger for quicker inventory calculations. My question is: If I'm to do this, am I simply changing the status of the transactions to archived and running the script to run through and find all the information that needs to go to a master record and of course have the inventory only calculate open tickets or am I actually moving these archived transactions to another table? I've currently removed the ability to edit a record once it's been printed but we have had to go back and tweak some information on records as old as a month or two on a regular basis ..... I'm guessing I would put a script in place that picks up any changes made to the archived records and update the master ledger records if needed?
Once we get this out of the way I would like to touch on a data base design that consists of having all the layouts in one file and data stored in another (I think this would be extremely beneficial to us -- Phil I know at some point you has given me a few links on this but I'd be digging for a while if I tried to find the post again) and touch on look-ups.
As always, appreciate the help!