Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-04-2017, 02:18 AM   #1
MattCass87
Newly Registered User
 
Join Date: Sep 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MattCass87 is on a distinguished road
Smile Database Plan for ordering system

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
Attached Files
File Type: zip MIS_System_Hierarchy.pdf.zip (13.8 KB, 30 views)

MattCass87 is offline   Reply With Quote
Old 09-04-2017, 02:56 AM   #2
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 3,736
Thanks: 46
Thanked 1,267 Times in 1,199 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Database Plan for ordering system

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-04-2017, 03:09 AM   #3
MattCass87
Newly Registered User
 
Join Date: Sep 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MattCass87 is on a distinguished road
Re: Database Plan for ordering system

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

MattCass87 is offline   Reply With Quote
Old 09-04-2017, 04:49 AM   #4
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 3,736
Thanks: 46
Thanked 1,267 Times in 1,199 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Database Plan for ordering system

We'll wait for the elders, they have more wisdom to share..
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-04-2017, 04:51 AM   #5
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 906
Thanks: 202
Thanked 109 Times in 100 Posts
NauticalGent will become famous soon enough
Re: Database Plan for ordering system

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...
__________________
...see, what had happened was...
NauticalGent is online now   Reply With Quote
Old 09-04-2017, 05:04 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,187
Thanks: 80
Thanked 1,141 Times in 1,117 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Database Plan for ordering system

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.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 09-04-2017, 09:32 PM   #7
MattCass87
Newly Registered User
 
Join Date: Sep 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MattCass87 is on a distinguished road
Re: Database Plan for ordering system

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 is offline   Reply With Quote
Old 09-04-2017, 11:04 PM   #8
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 8,654
Thanks: 243
Thanked 642 Times in 613 Posts
Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about
Send a message via Skype™ to Uncle Gizmo
Re: Database Plan for ordering system

Reading your question, I'm not sure you need this:- Allen Browne Inventory Control: Quantity on Hand http://allenbrowne.com/appinventory.html

But i thought it worth mentioning...

Sent from my SM-G925F using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
............

Join My YouTube Channel ---
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Hundereds of MS Access Videos!!!!!
Uncle Gizmo is offline   Reply With Quote
Old 09-08-2017, 07:27 AM   #9
MattCass87
Newly Registered User
 
Join Date: Sep 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MattCass87 is on a distinguished road
Re: Database Plan for ordering system

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?
MattCass87 is offline   Reply With Quote
Old 09-11-2017, 01:00 AM   #10
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,187
Thanks: 80
Thanked 1,141 Times in 1,117 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Database Plan for ordering system

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.

__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Tags
database beginner , database concepts , invoicing , ordering , stock control

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Ordering System, Form with Subform navi95 Forms 9 11-25-2016 08:07 AM
is this a sound database plan? bite_tony Theory and practice of database design 5 05-30-2012 04:00 PM
URGENT! Ordering System (Updating Stock Levels) TOSCS General 6 04-16-2008 08:28 PM
Trying to plan out a db. Plan is attached. Advice welcome. Colin@Toyota Theory and practice of database design 1 10-31-2007 12:09 PM
a good disaster recovery plan for my database lala General 6 09-21-2002 11:22 AM




All times are GMT -8. The time now is 11:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World