Database Plan for ordering system (1 Viewer)

MattCass87

New member
Local time
Today, 08:32
Joined
Sep 4, 2017
Messages
8
Hi all,

Please see below and attached. I’m creating a ordering system where employees can login and create an order. The order will also consider stock and eventually also work out costs.

When an order is raised, that order can then be made up of many parts. So ‘order 1’ could have ‘part 1’, ‘part 2’, ‘part 3’ all created on different days and with many different components.

Orders = List of parts
Parts = list of components

Components would be combinations of materials with sizes and quantities.

Component1: Qty: 10 Size: 1000x1000 Printer: Machine 1 Material: Vinyl 1 Material: Laminate 1 Finishing: Cutter 1 Packaging: Package 1

I would appreciate some feedback on the plan below, does the structure make sense? Or is it illogical?

This is quite a big task for me having not touched access for about 10 years so I want to make sure im on the right path before I start.

Thanks in advance for any advice
 

Attachments

  • MIS_System_Hierarchy.pdf.zip
    13.8 KB · Views: 238

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:32
Joined
May 7, 2009
Messages
19,169
ORDERS should be breakdown to OrderHeader and OrderDetail.
OrderID, EmployeeID, Description and ClientID.
PartID to OrderDetail, and the quantity needed to accomplished the Orders.

BTW this is a JobOrdering system, is it? and not orders of supply or materials.
 

MattCass87

New member
Local time
Today, 08:32
Joined
Sep 4, 2017
Messages
8
Hi Arnel,

It is indeed a job ordering system that will essentially produce print orders using various different materials, but will need the ability to understand stock use per job to allocate material per job and subsequently flag requirements to reorder

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:32
Joined
May 7, 2009
Messages
19,169
We'll wait for the elders, they have more wisdom to share..
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:32
Joined
Apr 27, 2015
Messages
6,280
Although I am be no means anyone's elder, especially Arnelgp's, I would suggest downloading the Northwind Traders DB and adapt it to your needs. It does everything you are asking and more...
 

Minty

AWF VIP
Local time
Today, 08:32
Joined
Jul 26, 2013
Messages
10,353
One thing with stock and quantities - you don't try and store an On-Hand value, you should always calculate it from a transaction table.

It should have stock in and out in the same column, positive for stock in and negative for stock out. You can then easily just sum that column by part number to give you the current balance.
 

MattCass87

New member
Local time
Today, 08:32
Joined
Sep 4, 2017
Messages
8
Thanks all - The Northwinds example is pretty great. I'd like as much as possible to build from scratch so I can gain the understanding required for future use, so this will be an excellent resource to break down.
 

MattCass87

New member
Local time
Today, 08:32
Joined
Sep 4, 2017
Messages
8
Hey,

So i've been tinkering around with relationships, building tables and trying things out but I'm struggling with the fundamental understanding of orders within orders.

Can anyone advise what relationships I need to complete the below:

1. Raise an order (e.g. order 1001)

That order is now raised however within that order over the next month I may make several orders within that order which would all be invoiced at the end. So I could have Order 1001 with part 1001-001, 1001-002 all with different products inside.

2. Create a part number for that order (e.g. Order 1001, part 001)

3. Add products to the part numbers e.g.

Order 1001, part 001, component 001,2,3,4,5,6,7,8,9
Order 1001, part 002, component 010,11,12,13,14,15,16
Order 1001, part 003, component 017,18,19,20,21,22,23

Am i looking at this completely wrong?
 

Minty

AWF VIP
Local time
Today, 08:32
Joined
Jul 26, 2013
Messages
10,353
I would say you are heading in the right direction. You just need to extend your breakdown of the orders to also make the components a single line entity.

Code:
OrderHeader¬
     PartLine¬
           ComponentLine
So each PartLineID would have the OrderHeaderID in it as a foreign key, and each ComponentLineID would have the PartLineID as a foreign key.

I would personally not try and "reset" the part line id to 001 for each order, it will cause you a whole heap of pain, similarlry with the component ID's.

Accept them all as unique autonumbers within their own tables, linked back to the "parent" record.
 

Users who are viewing this thread

Top Bottom