Lakeshore Technologies was founded in 1997 and has grown from a small start-up operation to their current 75,000 square foot facility. They offer a variety of toll processing services including micronizing, blending, repackaging, sifting, and metal detection and removal. Their ability to provide these services as a contract manufacturer helps their customers reduce costs and the need for capital investments. Their experience and commitment to quality has allowed them to partner with some of the largest companies in the food, pharmaceutical, and nutritional industries.
We began by documenting and diagramming the current structure of the database, in terms of both data and work flows. We then identified that the current data structures would not allow the updating of related products in the way that was necessary. For this reason, and due to concerns of multiple users accessing the database at once, we moved the data tables to SQL server.
In SQL server we were able to take advantage of SQL programming as well as many more advanced capabilities contained within. Data tables were redesigned to accommodate revision tracking of the primary objects within the required database.
Lakeshore Technologies’ database now accurately, and automatically, tracks revisions made to a material, all of the products the material is used in, and any other products the first set of products may be used in, until all affected items have been updated. None of these updates affect previous records of incoming materials, or previously produced Lots. All of this creates an accurate history of exactly what a product consisted of at any given point at which it was produced.
With the data tables moved to a backend server, and the Access front end, multi-user accommodation is substantially increased. Much of the automatic updating, and many other resource-heavy queries, were able to be moved to the server. This has improved performance overall.