8 Replies - 1278 Views - Last Post: 07 July 2016 - 08:27 AM Rate Topic: ***-- 2 Votes

#1 Mentos93   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 05-July 16

Database design for a purchase module

Posted 05 July 2016 - 02:13 AM

I'm an intern doing a project for a company and need some serious help. I'm busy wasting time with figuring out what the best design will be for a basic database where an order can be made, a supervisor can be assigned for that specific order to approve or disapprove it, and to log the actions.

Another part that screw me is that the users are in a total different database.

Below is my ERD what I currently have, but it doesn't feel right.

Please note: The two tables at the top with the purple box around is from another database. The right one (werknemers) is users and I need the users from that table in my project that make use of another database.

Some extra information about the project The project is about a purchase module. Employees that need something for their job can make an order on this system and assign it to their supervisor. The supervisor should approve it before the employee can order it. The order can also be disapproved. In some cases the supervisor might need to send it for a second approval (that's why I don't have just another field in my order table approver (supervisor).

This whole process needs to be logged as well. Like this;

Person x requested order on date xx-xx-xxxx
Person x sent request to supervisor y on date xx-xx-xxxx
Supervisor y approved order on date xx-xx-xxxx

and so on.

I desperately need help with the Database design because it feels wrong. I just need opinions and how you would've done it.

I use PHP with the Symfony framework and I don't get how I should update a table like approval to set the approver that should come from the users (werknemers) table when I create an order.

Please. I'll appreciate it a lot!

I don't understand where my attachment went!

My Database Diagram is now attached

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Database design for a purchase module

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2588
  • View blog
  • Posts: 10,371
  • Joined: 03-December 12

Re: Database design for a purchase module

Posted 05 July 2016 - 04:12 AM

We're you given a mentor to guide you?
Was This Post Helpful? 0
  • +
  • -

#3 Mentos93   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 05-July 16

Re: Database design for a purchase module

Posted 05 July 2016 - 04:51 AM

View Postastonecipher, on 05 July 2016 - 04:12 AM, said:

We're you given a mentor to guide you?


Yes, but he is not really helpful, he always talk about 15 minutes a day and then I don't see him anymore. It feels as if I annoy him. And now he is gone for two weeks on summer holiday and expect some good progress from me when he gets back.

I don't want to wait for him, because I sit and waste time. I just want to try and continue without him.

So hopefully somebody on the big web can still help me.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2588
  • View blog
  • Posts: 10,371
  • Joined: 03-December 12

Re: Database design for a purchase module

Posted 05 July 2016 - 06:29 AM

What kind of orders are these for, anything?

pmod_approval->approver should have a userID as a foreign key.
You are limiting the order id a bit much, unless things are rarely purchased. 9999 seems a bit low for a long term purchasing system.
What is the purpose of the pmod_log table?
Was This Post Helpful? 0
  • +
  • -

#5 Mentos93   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 05-July 16

Re: Database design for a purchase module

Posted 05 July 2016 - 06:55 AM

View Postastonecipher, on 05 July 2016 - 02:29 PM, said:

What kind of orders are these for, anything?


Yes, the company is an internet service provider. Thus modems is required if modems are running out. Set-top boxes for TV, etc. and then for stuff like new office equipment whatsoever.

View Postastonecipher, on 05 July 2016 - 02:29 PM, said:

pmod_approval->approver should have a userID as a foreign key.


I'm completely aware of that, but the users table is in a complete different database, and I don't know if that will work then.

View Postastonecipher, on 05 July 2016 - 02:29 PM, said:

You are limiting the order id a bit much, unless things are rarely purchased. 9999 seems a bit low for a long term purchasing system.


Thanks for this tip, will change it.

View Postastonecipher, on 05 July 2016 - 02:29 PM, said:

What is the purpose of the pmod_log table?


The system is supposed to keep logs of each order. When it's created, by whom, who approved it, is it changed and by who and when. That kind of tracking.
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2588
  • View blog
  • Posts: 10,371
  • Joined: 03-December 12

Re: Database design for a purchase module

Posted 05 July 2016 - 07:10 AM

Regarding the last statement, that should be handled in the order table. Created is there, last modified would be useful.

The proper query would be able to pull that from the respective tables as well.
Was This Post Helpful? 0
  • +
  • -

#7 Mentos93   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 05-July 16

Re: Database design for a purchase module

Posted 05 July 2016 - 07:51 AM

View Postastonecipher, on 05 July 2016 - 03:10 PM, said:

Regarding the last statement, that should be handled in the order table. Created is there, last modified would be useful.

The proper query would be able to pull that from the respective tables as well.

Ok I understand.

But do you think that entire database like you see it will work? Because the approval table feels really awkward, because of the fact that the approver-user should come from an entire different database.

And it really cracks my skull to think how I should program the method to create an order, and assign an approver from another database.
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2588
  • View blog
  • Posts: 10,371
  • Joined: 03-December 12

Re: Database design for a purchase module

Posted 05 July 2016 - 03:42 PM

Multiple databases
Was This Post Helpful? 0
  • +
  • -

#9 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Database design for a purchase module

Posted 07 July 2016 - 08:27 AM

astonecipher's link is good if your logic is not database side.

As for talking between databases at the query level it's really just as simple as specifying the name of the database when doing your joins, etc... unless the database is on a completely different server. If that is the case then you will need to look into Linked Servers.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1