About ERD, primary key, and cardinality?

[wannabesmart]

What do you think about its cardinality?
Is it right n:1 (many to one)?

As you see in the relationship (in the diamond shape), there are two new primary keys (PurchaseID and InvoiceID) which are not from both entities (Product and Supplier). So, is it possible? or any suggestions about the right ERD?

Another question is where should total (using formula) be placed? as a textbox in a form or in a query?

I'd be very grateful if anyone could help

[June7]

Conventional relational database wisdom is to not save calculated data, save raw data and do calcs in queries.

Whose purchase order and whose invoice.

Is this your purchase order and you want to relate it to the supplier's invoice to you?

Review http://allenbrowne.com/AppInventory.html

[rpeare]

I agree with june, do not store calculated values, bad idea and very hard to maintain correctly.

Second, I think your model may be a bit too simple.

For instance, on your Products (PRODUCTID (PK), ProductName, Type) are all good but your prices for both buying and selling goods change over time and if you want to be able to re-produce an invoice you'll need to be able to calculate the prices correctly for a specific moment in time. I would also assume you'd want to do some sort of analysis over time to see what your costs are and knowing the history of pricing is important for that.

Second, on your supplier side, you can have multiple contacts per supplier, so your supplier table is fine but your contacts at the supplier should be a child table to your suppliers, especially as your contact as the company may change over time, and your 'supplier' table should also contain your 'customer' information as well, there's no reason to separate the two unless you are tracking radically different data which most people do not. You can indicate which is a supplier, which is a customer (they can be both after all) with simple checkboxes or yes/no fields.

Finally, your invoices should consist of a main invoice table, and a detail table. You can use this structure to both receive and issue items from your inventory (don't know if your goal is only to be able to produce an invoice or use this as an inventory management solution as well, it looks like that's what you're attempting but I can't really tell for sure based on this diagram). So your 'main' invoice table would contain a reciept/sale indicator, the supplier/customer ID, an invoice number (suppliers will be giving you theirs, you will be issuing your own) and primary key (autonumber works fine) along with whatever other information you want to track (sale date/receipt date).

Your detail table would contain a line by line list of products and quantities and an itemized cost associated with them on receipt/sale.