Hi Gerry,
keep each version of each product's description in the products table, along with the date that description became valid. Then the product ID and date in each transaction would reference the appropriate product table data.
In certain circumstances, I can see how that would be useful. However, it's a problem here because:
1. The products table has many more rows and columns than I need to copy into the occasional sale. So maintaining the products table beyond this need is cumbersome.
2. When a product changes (such as price increase), I don't want to add a whole new product_id and mostly duplicate information, into the already huge products database.
3. 99% of the items in the Products database get deleted without any sale being made against them. So keeping them all around for historical reasons would multiply the size of the database many fold. It also raises issues of tracking what products can be deleted and what needs to be kept since a sale used it.
4. I only need to "track" a change in a product if a sale is made against it. So it makes sense to capture the details of that product in the sale when the sale is made.
So, what I need is, that when (and only when) a product is added to a sale, then that product's buy price, sell price and description are stored against that sale.
I hope that clarifies the situation. Thanks for your thoughts and time. Tom ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
