I am a little lost in my solution regarding my approach. I am trying to get a solution that builds transportation quotes for clients, then transform them into bookings/reservations.
My current structure/thought process is:
Some of the questions that have been haunting me are:
1) A person can make a reservation straight away, without having to quote - which means they would have to have almost all the same fields(with the exception of quote-only fields), to handle any situation. Would it be best to keep both under the same table with a flag field to differentiate between Quote/Reservation?
2) If I keep them into separate tables, for the sake of isolating them better, what would be the best way to convert parent/child records from one to the other, in a reliable manner? I know we can do export/imports, and I've also used a few different techniques, I'm just worried this might not be effective when 20+ people are using it at the same time.
3) When assigning a client to a quote, I'm using a drop-down list with auto-complete, to make it user-friendly. However, I am doing a lookup on the foreign key based on that name match coming from the client names value list, and I know that isn't good at all because if the client gets his name changed, it won't update towards the quote. How can I keep the auto-complete while assigning the number foreign key effectively??
I will appreciate any answer, opinion or thought on this A LOT! Thank you for your time and sorry for the long post.