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.
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.