Hi Everyone, the operations manager was so impressed with the CRM / Job tracking database I set up for my department ( thank you to this forum and Bruce Myrons Udemy courses), he has contracted me to help build a job tracking database for the jobs going through the factory. We're a large scale custom cabinet manufacturer. I have some ideas of how this should work, but I was hoping to get some advice from the experts on here. I'll provide some background on the flow of work for the order.
Once the order is approved it goes through accounting then onto scheduling, which is where the database will start tracking the order. Each order consists of 1 or more "sections". Think for example you would have a different cabinet finish in your kitchen, bathroom, laundry room etc. The order actually starts production 13 working days before target delivery, even though the orders are scheduled out 30 to 50 working days.
Scheduling will enter the order onto the tracker, and schedule into the target week for delivery. This will vary based on the product (paint and stain take longer than melamine, custom work takes longer, etc). Scheduling will put it onto a target day for the target week, based on how many jobs are going that week. I have an idea on how to set up the forms with subforms to be able to see dates.
After scheduling, the order goes through admin (engineering and purchasing), we will need to track the date of completion at each step through this process.
After this it goes to the shop for production. At this point they are not looking at tracking through here, but I would like to set up the DB in a way that I can expand into that functionality. I would also like to be able to expand in the other direction to be able to build a client database for use for the sales people.
My thoughts are setting up a table for the order, a table for the sections of the order, and a table for the completion dates as it goes through admin. The second 2 tables linking back to the first.
I know this is a ton of information, but I was hoping to get some tips from more experienced database designers on anything I may have missed or not known while setting this up. I'm happy to chime in if I might have missed any important information in this post. I really want to be able to set this one up right from the start, unlike the last one where I had to work around some noob mistakes.
Thank you.