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

    Subcontractor Pricing+Invoice Tracking Database - On the right track?

    I'm working on setting up a database to keep track of all of my company's (we're a masonry subcontractor) quotes from suppliers, invoices, etc. to streamline a few people's jobs. It's a pet project of mine.

    I'm the estimator, so trying to set the DB up to meet all my needs, and leaving the door open to meet those of our financial person as well if they like what I came up with. Below is the design I've come up with, and I'd like to know if it looks like I'm going in the right direction, or if there are any issues that a more experienced user might notice.

    Click image for larger version. 

Name:	My Database Diagram.jpg 
Views:	32 
Size:	75.1 KB 
ID:	43756

    For reference:


    - We order many products, from many manufacturers, who provide us with many unique quotes, invoices, receipts, etc. (I've put each of these in "tblPriceSources")
    - Each product may be carried by many manufacturers, each having their own price which may change depending on many factors I don't need to track. For putting together MY quotes for our GCs (General Contractors), I usually use the lowest price that is likely to be honoured, which I'm going to write a function to calculate and store as "LastPrice" in "tblSupplierProducts". The price actually paid on an invoice/receipt is stored separately as "PricePaid" in "tblPriceSourceLine".
    - Each quote/invoice/receipt may contain many products, from many manufacturers, for many jobs, from one and only one supplier.
    - ***I want to be able to generate a list of all items in "tblPriceSourceLine" that were actually purchased for a job, by returning all rows of "tblPriceSourceLine" for a specific "Job" having only those "SourceTypeID" of which correlate with an actual purchase (i.e. excluding "SourceTypeID" which are from supplier quotes , e-mails, verbal pricing, etc). I think I can do that with the current design, but not certain, since the relationship between "SourceTypeID" and each item in "tblPriceSourceLine" is only defined by going through "tblPriceSources". Is this correct?

    Besides that, I think my labels are fairly self explanatory. I thought I'd explain the following columns though:
    - "SourceName" could be "invoice", "quote", "e-mail", "reciept", ...
    - GC means "general contractor"
    - "tblPriceSourceLine" contains all line items from all price sources, and is linked directly to "JobID" since one invoice could contain products for many jobs.

    So, how close am I? (I did notice that "GGID" is a foreign key in "tblJobs", but couldn't be bothered to edit the diagram just for that)

    Also, if it matters, I'm creating this database with PostgreSQL, since there's a lot more flexibility than with Access.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure why you need ProductID in tblPriceSourceLine. If my understanding is correct, it should be irrelevant because you can track back to tblSupplierProducts.

    Also suggest do not use split FK fields as PK - by all means have a multifield index to not allow duplicates but have a single autonumber field as a PK

  3. #3
    quarky2001 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2019
    Location
    Edmonton, AB, Canada
    Posts
    9
    I see what you're getting at with the first point about ProductID.

    Curious about the two far right tables though, as far as using a composite primary key rather than an autonumber --> I might be understanding joins/junction tables wrong, but I thought the 2-field primary key was how a many-to-many relationship was usually achieved? I can see advantages to having a unique single field key, but how would would the join occur?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I thought the 2-field primary key was how a many-to-many relationship was usually achieved?
    no, you use a junction table for many to many relationships

    tblSuppliers
    SupplierPK
    SupplierName

    tblProducts
    ProductPK
    ProductName

    for a many to many relationship, a junction table

    tblProductSuppliers
    ProductSupplierPK
    ProductFK
    SupplierFK

    tblSources
    SourcePK
    SourceName

    another junction table

    tblPrices
    PricePK
    ProductSupplierFK
    Price
    PriceDate
    SourceFK


    tblJobs

    JobPK
    JobName

    and perhaps another junction table

    tblJobPrices
    JobPricePK
    JobFK
    PriceFK
    Quantity

    with your structure, you can only ever have one price per product/supplier. With the above you can have many prices (perhaps varies due to volume, or you still have some old priced product in stock, etc). Your structure may work for you, you know your business better than I do.

  5. #5
    quarky2001 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2019
    Location
    Edmonton, AB, Canada
    Posts
    9
    Ah, I think I may understand a bit better now - When I look at your model, it made me realize that my composite primary keys would make it basically impossible to refer back to a specific row of the table, especially if it needed to be edited (is this right?).

    Also, using tblJobPrices in your model as an example, is it enough to define the fields "JobFK" and "PriceFK" simply as both being foreign keys for the many-to-many relationship to occur, or are more steps required? I ask because if I was to define my tables and fields as I did in my first post, then if I run 'SELECT * FROM tblSupplierProducts', then I get a tables listing every product carried by every supplier without further input, but my spidey sense tells me there's something I'm missing to make the autonumbered index with the two foreign keys work similarly.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

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

Similar Threads

  1. Invoice Tracking Database design
    By quarky2001 in forum Database Design
    Replies: 5
    Last Post: 12-21-2020, 07:57 AM
  2. Complicated Invoice tracking.
    By Homegrownandy in forum Access
    Replies: 8
    Last Post: 11-10-2015, 11:45 AM
  3. Need inventory tracking and invoice forms
    By jayvan39 in forum Forms
    Replies: 33
    Last Post: 02-23-2013, 01:12 AM
  4. Invoice tracking
    By Amber in forum Database Design
    Replies: 1
    Last Post: 02-02-2012, 12:24 PM
  5. Pricing Database
    By nsvorp in forum Access
    Replies: 6
    Last Post: 09-10-2010, 10:33 AM

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