3 Replies Latest reply on Jun 4, 2015 8:48 AM by burghfan

    database change


      Any help or input, would be appreciated.


      I belive I made a mistake up how our database is operating.


      When I first laid it out the database, I thought it would be best to have the creation of "New"; orders, and estimates all start from a specific customer.


      As our business model has changed I now see the need to be able to create "New"; orders and estimates from their own TO. The reasoning behind this change is that now some of the "estimates" that we create will need to be associated to multiple customers during the pre-purchasing phase of the sales cycle.


      I'm also thinking that it would be a good thing, that once an "estimate" has been awarded for purchase, to have the ability to convert it to an "order".


      To make these changes I'm thinking I'll need a related "customer" portal in both the "orders" and "estimates" TO's. There would also be the need to select a particular "customer" portal row from the "estimates TO, once the estimate has been awarded for purchase to that specific customer. I was planning on using a pop over button to select the "customers" for populating the "customer" portals.


      I would imagine I'll need a script to take the "awarded" estimate info over into the "orders" TO.


      I've probably missed several key items, and thats where any suggestions would come in handy, thank you.

        • 1. Re: database change

          Sounds like you need a join table between an estimate and customers, to show what customers have received the estimate.  A wrinkle may be however that if one customer asks for a change and you don't want to affect the others.

          Or one customer gets a different price tier than another...

          In that case you may have to duplicate the estimate.  But if then need to make a change that affects all "grouped" estimates...

          It can get pretty complex so make sure you spend some time going over all possible scenarios before making an architecture decision.


          As to converting an estimate to an order: definitely script it so that it becomes its own records.  Orders can still change and you don't want it to affect the original estimate.

          • 2. Re: database change

            Before thinking too much about the HOW (whether to use popovers, scripts, portals, etc) I suggest you focus on, and get clear in your mind, the basic underlying data structure—what data you need to store, what tables you need to store it, what pieces of data go in which tables, what relationships you need to put in place to connect up the pieces. And of course all of that flows from writing down a clear, simple statement of the purpose of the database. e.g.:


            This enterprise supplies xxx service/product to customers. Initially we put together estimates based on our understanding of what the customer requires. A successful estimate becomes an order, a completed order requires an invoice … etc etc blah and blah

            • 3. Re: database change

              Thanks for all of the suggestions they are all appreciated and I'll be implementing what I can shortly.