Is my Table Structure Correct? (1 Viewer)

Hi,

I'm in the early stages of developing an inventory database for a production company I work for. At the moment records are retained on a bunch of excel spreadsheets!.

I wish to be able to place a Purchase order for multiple raw materials from a supplier (of which there are several) & have appropriate pricing against each line item.

I have this,

TBLRawMaterials
MaterialGrade - PK
Supplier - FK

TBLMaterialPricing
PriceID - PK
MaterialPricingLink - FK (MaterialGrade is linked to MaterialPricingLink)
Price_$
Valid_Till

TBLSupplier
SupplierID - PK & linked to TBLRawMaterials.Supplier

TBLPurchaseOrder
POID - PK
SupplierID - FK & linked to TBLSupplier.SupplierID

TBLPOItem (the sub sheet displaying the filteredd line items)
POLineID - PK
POID -FK & linked to TBLPurchaseOrder.POID

My intention is to purchase from an approved supplier & could have several lines of items per PO.

I have a FormPO with subform to display the PO line items, but I am unsuccessful in having the latest pricing appear beside each line item. On FormPO I filter for an "Approved" supplier & select a material in the sub form accordingly. That works!

Is my structure wrong? If not, how do I have the latest pricing appear beside each line item on the sub sheet?

I've received great support from the forum so far. Appreciate any advice.

Cheers Terry

Looks like a good start. Might want to review http://allenbrowne.com/AppInventory.html

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Those hyphens probably not meant as part of names but does appear to be a $.

Changing pricing issue options:

1. save the ProductID and use code to also save the associated price

2. new record for every new product/price, tag out of date product records as 'inactive', save only the ProductID

Use a multi-column combobox that has the ID, product, and price. Reference column of combobox to display price in a textbox and/or save to record.

Hi all, what I really want to do is recall the latest price by date & use this in the calc of unit x price, but retain previous history.

Option 2 is one approach. Product combobox choices would be filtered to only the most recent price because previous are marked 'inactive'. Save the ID of that record into order. History of orders and products preserved. Prices are retrieved in report queries by joining tables.

Newly, clearly your suggestion is an option. However, I'd really like only the latest (by date) price to display.

Display where? And pulling from where?

For a combobox, filtering out the 'inactive' product pricing is easier than pulling by a 'latest date' value. The end result should be the same.

Hi, when I add a new PO I wish to select a supplier in the main form, then build a datasheet fomat of the raw materials as a sub form within the main that is sorted to return the material & the latest price from the pricing table.

Right, use comboboxes that list suppliers and materials/price. Might want to use cascading comboboxes. As I said, the 'latest' price records can be retrieved by applying a filter for only the active price records. Filtering on the Valid_Til date is possible but a yes/no field filter is simpler.

In addition to the other suggestions.
1. Be consistent with names. Primary keys that are autonumbers usually have an "ID" suffix. Use the same name when you create a foreign key. So if the PK is MaterialID, then the foreign key should also be MaterialID. In rare cases, you might need two or more references to a table. For example, if you have a shipping and a billing address, you will have two StateCD's One should be named Billing_StateCD and the other should be Shipping_StateCD.
2. Do you really ever have only a single supplier for a material or might there be multiples in some cases? If you could have multiples, you need to use an autonumber PK and a two part unique index to ensure the business rule of uniqueness for the combination materialGrade + SupplierID