Hi everyone. I'm building a new database for the business I work for. It's a printing company. I have a good understanding of the business and am fairly certain of the different tables I need to build to accomplish what we are looking to achieve but would love some feedback from people with more experience than I have (which is only about 8 months in total) with building solutions/applications in FM. Anywho, Here is a basic rundown of what I think I need. I've attached screenshots of my tables and relationships. The one question I have is how to handle the relationship between the Quotes table, Orders table, and Line Items tables ( 1—5 ) and/or whether there should be more or less tables than what I've already created.
One of the reasons I have questions about this is that some quotes that our CSRs do are for 5 different quantities of the SAME line item (product) but sometimes it is a quote for 5 DIFFERENT line items (products). I'm looking for the best/simplest way to handle each of these situations before I start really diving into this because I know if I don't plan this properly, it's going to turn into some Frankenstein monster. My initial thought when I started thinking about this was that all of my costing calculation fields would be a part of the Line Items table. That way I would only have to create the fields and write each of the calculations one time but now I'm not sure because of the whole quoting multiple quantities of the same line item scenario.
Should I have a separate table for each line item on a quote as well? Should I just have a single line item table instead of 5? (My reasoning behind having a separate table for each line item is for the production side of the business that will see each individual line item and it's location on the shop floor in a list view) Maybe I'm looking at this whole thing the wrong way. If anyone has any better ideas, I would greatly appreciate hearing them. Like I said, I have minimal experience with FM and just want to make sure I at least have a good starting structure before I get too deep into this. Let me know if you need more information than what I've provided. Thanks in advance!
P.S. I've also attached a screenshot of our current database just so you can see what we are leaving in the dust bin of history. Maybe there are some good things about this that I should be using but I haven't been able to see its merits....again...NOOB.