Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2016

    Database design for an order creation system that require approvals

    Hello everyone!

    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).

    Click image for larger version. 

Name:	2. New Order.png 
Views:	30 
Size:	47.0 KB 
ID:	16990

    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.)

    Click image for larger version. 

Name:	5. Approve Order - b.png 
Views:	2 
Size:	89.8 KB 
ID:	16991

    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?

    Click image for larger version. 

Name:	PModERD.png 
Views:	7 
Size:	65.7 KB 
ID:	16992

    I really need help, if anybody can please advice me and give some input and advice.

    Thank you very much in advance.

  2. #2
    Join Date
    May 2016
    Hi Mentos93

    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

    Attached Thumbnails Attached Thumbnails purchasePhysicalDM.jpg   purchaseConceptualDM.jpg  
    Last edited by informer; 07-10-16 at 15:31.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts