Fog Creek Software
Discussion Board

Database table design suggestions??

Hi, need some suggestions on a database table design. I am using Access / VB6  and here is the scenario. I will try to be as clear as possible.

I am designing a simple Invoice system which links to a Inventory system. Once invoice is created, stock will be reduced from the Inventory. But I am thinking of making this SAME program also work for customers who don't need the inventory part.

The Invoice table is linked to InvoiceDetail table. The Product table is linked to the Invoice Detail (one to many).

How to solve this problem for InvoiceDetail records that do not need Product table info since referential intergrity rules state that Invoice Details must  have a foreign key from Product table.

I just need some suggestions and I can figure it out. Thanks again.

Saturday, September 6, 2003

Obviously your rule is that those detail records DO NOT need a FK, so you must adjust to that.

Either you do, or do not allow records without the FK. I see no reason at all to enforce the fact that detail records must have a product id. Why?

I think the real problem here is are you actually going to write code to update the inventory levels, or use a code free solution, and calculate the inventory on the fly?

Depending on the type of inventory, NOT using any code to update the inventory levels will SAVE TONS of code.

You can read the following notes on mine on this issue. The following link is right into the middle of my article where I talk about stored vs non stored inventory values.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. kallal
Saturday, September 6, 2003

When you say 'Inventory', are you referring to the amount of stock on hand, or are you talking about a product list? Even if you are not including the 'Inventory' module for certain customers, it seems to me that you still need a 'Products' table -- although this Products table will just have info like Item #, Name, Price, etc., WITHOUT tracking the stocking levels of those products.
If you don't even want a Products table to use for the Invoices, then that means the customers need to enter in 'manually' the products on the invoice each time. This seems like a very counter-productive "feature", since in my experience, one of the most important reasons for a computerized invoice system is to reduce mistakes in data entry and to be able to easily do reporting of order totals. If you don't have a Products table at all, then you are leaving the whole process very error-prone; and you cannot do accurate reporting about total dollar amount of products ordered (because the same product may be spelled different ways in different invoices, for example).
Did I understand your question properly?

  -Jordan Lev

Jordan Lev
Saturday, September 6, 2003

Do NOT link your inventory to your invoices. From a coding perspective it seems obvious that invoice item and inventory item is 1<->1 (one item on invoice is one less item in inventory). But fiscal realities speak otherwise.

You can invoice for product that will ship later, invoice for backordered materials, reinvoice, etc, etc. That's not to mention simple errors like duplicate invoices or items left off invoices.

Your thinking should be

----herein lies auditing----

<=>  indicates where your governing truth comes from. If your Inventory is in doubt, you hit the floor counting bins. If your invoicing is in doubt, you check the accounts. Once those two relationships have been validated against ground truth, then you figure out where the disconnect is between them.

BTW, not sure how deep you are in this project, but realize that
a) Business systems
b) Document systems (invoicing)
c) Stock systems (inventory)

Are *each* an enterprise solution that require intensive effort. If that's what you're doing, they should be loosely coupled so they can be delivered independently.


Saturday, September 6, 2003

Thanks for all your feedback. The problem is I am trying to make this Invoicing system cater for customers needing the Inventory part included with their Invoice system and other customers just wanting Invoices with no fixed items etc transportation, repair charges.

If this can be done, then the same system can be used by both sets of customers without any changes.

Thanks all have been much help.

Saturday, September 6, 2003

Introduce an Inventory table linked to Product:

Inventory(productId, count)
InvoiceDetail(invoiceId, productId, ...)

Now Invoice is independant of Inventory.

Thomas Eyde
Saturday, September 6, 2003

In your case both Invoice and Inventory (item) are strong entities.  Then you need an association table between the two.

And it seems this is always the case since the Invoice-Inventory (item) is a many to many relationship (one invoice has 1 or many items; one item is in 0 or many invoices)

Saturday, September 6, 2003

*  Recent Topics

*  Fog Creek Home