07-06-16, 06:59 #1Registered User
- Join Date
- Jul 2016
Database design for an order creation system that require approvals
I am busy with developing a system that allows users to place orders within an organization. The user simply have to fill a form in with the details of what he wants. In the same form the user should select a supervisor to review is order to approve or disapprove it. (See the attached image for a wire frame).
When the order is submitted, the supervisor should get an email with a link to that order. When the supervisor opens that link he sees the complete order just like the user created it, but with three extra items. A button to approve the order, a disapprove button, and a check box to enable a field to choose another person for a second approval.
Below that there will be a log to keep track of all changes and updates. (See the next wire frame.)
After that there will be some more steps but I don't think they are that important right now. I am working with two databases. One database (the old database) contain the users for the organization and the second database is for the new system. My main problem is the database design for this concept. The users that will use this program as well as the supervisors should come from the users table from the old database. What will be the best way to handle the approvals? Remember, sometimes it might occur that a second approval is required, therefore I can't just add a field to the order table for the supervisor it get sent to, because there can be two. Please see the next image for my ERD of what I have so far. (The part in the purple block is the old database where 'werknemers' are the users table).
And then another question, what will be the best way to handle the log?
I really need help, if anybody can please advice me and give some input and advice.
Thank you very much in advance.
07-10-16, 14:50 #2Registered User
- Join Date
- May 2016
I propose you this conceptual and the physical data model in accordance with the following assumptions
- The approval is on a purchase not on the detail/items (compound)
- you want to keep historic of different supervisor status for a purchase
Last edited by informer; 07-10-16 at 15:31.