Results 1 to 6 of 6
  1. #1
    quarky2001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Edmonton, AB, Canada
    Posts
    9

    Invoice Tracking Database design

    I'm looking for some help setting this database up, and its requirements are a little different from any tutorials I've been able to find.



    Here's what I'm going for. I own a construction contracting company, and I'm trying to keep track of invoices based on supplier, product, job for which the product was purchased, as well as quantities, prices, dates, etc.

    I've started out with four tables, with the fields in brackets: tblSuppliers (SupplierName, Address, Phone), tblInvoices (SupplierName, InvoiceDate, InvoiceNumber), tblJobs (JobName, GeneralContractor, POnumber), tblProducts (Manufacturer, ProductName, ...), and that's where I get confused.

    Here are the basic rules I need the database to allow for:
    - Each invoice may contain multiple products from a single supplier
    - Each supplier may change a product's price on every invoice, although they usually remain the same. That is to say, I want each product to have a "default" price for each supplier, but I should be able to change this "default" price without affecting pricing on past orders)
    - Each product may be carried by multiple suppliers, and at different prices
    - Each order will contain multiple products (from the same supplier), each product having a quantity, and unit price (which is specific to the product AND supplier)

    I know that the having multiple products on one order, and multiple orders for the same product means that I need a junction table, but I'm not sure how to deal with all the pricing variability, while at the same time being able to create a form that auto-fills the product's price based on the supplier and the product name, with the ability to either change the product's price on a one-time-basis, or update the supplier's prevalent pricing without changing that product's price on previous orders.

    At the moment, I'm mostly concerned with what tables I need for this to work, and what relationships and junction tables need to exist for my database to get off to a good start. Any input is much appreciated!

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    So these invoices are bills from the supplier sent to you?

    This is me shooting from my hip. I'm an amateur and this might be a terrible design... :
    Click image for larger version. 

Name:	Untitled.png 
Views:	45 
Size:	25.6 KB 
ID:	43600

  3. #3
    quarky2001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Edmonton, AB, Canada
    Posts
    9
    Thanks - that actually makes more sense the more I look at it. I'll let you know how it goes!

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    On second thought, since one invoice has only one supplier I'd think about these changes:
    1. Link the invoice to the supplier
    2. Remove the link from invoice_product and supplier_product tables
    3. Link the invoice_product directly to the product table.

  5. #5
    Madmads is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2018
    Posts
    15
    I just jump intor this discussion - does anynone know where I could find invoicing templates - for free? A bit close to the project management delivered by Microsoft? Thanks a lot . Best Mads. NB please remove this or warn me if posting in the wrong place.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link to a Customer- Order- Invoice model from Barry Williams' site. It may help with tables and relationships. It is meant as a "generic model" and not all of its components may be relevant to your situation. You can adjust, add as necessary.
    Test your model with test data and test scenarios --adjust as needed and re test until you're sure the model supports your requirements --then, build your database from this blueprint.
    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Design Help: Lien Waiver Tracking
    By twarner3 in forum Database Design
    Replies: 8
    Last Post: 12-03-2023, 04:23 PM
  2. Database design for grading / profile tracking
    By zoiets in forum Database Design
    Replies: 4
    Last Post: 11-29-2018, 02:39 AM
  3. Employee Time Tracking Database Design
    By shell159 in forum Database Design
    Replies: 1
    Last Post: 01-12-2016, 02:58 PM
  4. Asset tracking database design question
    By ittechguy in forum Database Design
    Replies: 9
    Last Post: 11-09-2015, 04:41 PM
  5. Help With Patient Tracking Database Design
    By wkenddad in forum Programming
    Replies: 2
    Last Post: 04-25-2012, 09:15 PM

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
  •  
Other Forums: Microsoft Office Forums