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!