Hi Puneet,
ok, your problem makes a bit more sense
Great :-)
but still, much in it doesn't make sense.
OK, I'll see if I can clarify further.
even if the products table is changing, the buy, sell, and
description of a given item shouldn't change.
The buy and sell price of products always change, due to inflation,
sales, competition and so on. Pick up just about any grocery or
electronic goods catalog today and next month and compare prices on
the same products.
Further, even if buy and sell change, then at least the description
will be not be different.
True, the description changes less often than prices. However, it
does change. It may be as small as a spelling correction, or have
some promotion attached eg "Now 20% bigger", or just a refinement of
the description without a change to the actual product. In all cases,
it is important that a particular sale shows the price and
description (and any other details) that were listed for the product
at the moment is was bought/sold.
If none of that apply as you imply above, then it is no longer the
same product... it is a different product altogether. In other
words, you can still identify each product with a unique id
What you say is true if it is a completely different or even updated
model/product. You would expect that the product_id would also change
for such a major change. However, this type of change is not what I'm
catering for. And, in any case, the product_id of each product is set
by the suppliers, so I have no control over it. And yes, I could
introduce my own product_id to override theirs but that's getting way
of track and doesn't really occur here anyway.
and one of the basic rules of normalization is that all related
things should be together.
Yes, I understand that. But I also hope I'm getting the point through
that the price is not static for a given product, and to a lesser
degree the description may change slightly, and I need to keep a
record of the product price and features as it was when purchased.
So, all information about a product should be in the products
table, not in the sale_products table.
The sales_products table is a many to many table, linking many sales
with many products. One sale may contain many products. And one
product may appear in many sales. To my knowledge the only way to set
up a many to many relationship is to create an intermediate table in
this way, even without consideration of the changing price etc.
Take a bar of soap -- bought it for $2, sold it for $2.40,
description is "woodsy, honey dew cataloupe smelling hand crafted
soap." A product_id of 243 identifies that entity uniquely. If you
change its attributes, say now it is, "viscous, tar-based paste
guaranteed to get motor grease off" then it is a completely
different product, and should have a different product_id.
Good example, I'll use it. I sell that soap today to Bill for the
price shown in today's catalog, at $2.40. But next week we're
overstocked, so I sell it to Ted for $2.10. And next week I revise my
catalog after having realized that people in my state don't know what
cantaloupe is, but instead call it "water melon" (true story). The
following week the "Hand Crafted" guild tells me that the name is
trademarked, so I change it's description again before selling it to
Neo. And a month later, the product is completely removed from my
catalog since I can no longer source it (Hand Crafted Guild under
suspicion of arson).
So, in each case, I need to capture the current product details for
the invoice/sale. I need an exact record of what I invoiced each
customer for. I need to know that I sold it to Bill for $2.40 when it
was called "cantaloupe", even though the last product catalog shows
it at $2.10 and called it "water melon", and especially since it no
longer appears in my current catalog in a month's time.
So, my sale_products table looks like this, in part:
sale_id product_id buy sell desc
1001 243 $2 $2.40 woodsy, honey dew cataloupe
smelling hand crafted soap
1013 243 $2 $2.10 woodsy, honey dew cataloupe
smelling hand crafted soap
1042 145 $5 $6.00 white rabbit tattoo remover
1042 243 $2 $2.10 woodsy, honey dew water melon
scented hand crafted soap
1042 176 $4 $5.10 red pill
1058 243 $2 $2.10 woodsy, honey dew water melon
scented home made soap
And sales contains (simplified):
sales:
sale_id customer date
1001 Bill 2007-06-18
1013 Ted 2007-06-24
1042 Neo 2007-06-30
1058 Morpheus 2007-07-04
Even if the product table is updated to capture new items from the
different catalogs, it will forever store the attributes of each
product, creating a unique history right there.
No. The product catalog/table is orders of magnitude larger than the
sales. I might sell 1 in 1000 of the products in the products catalog
each month, and the catalog will probably change 50% of its contents
every month (deleted products, new products, changed prices, refined
descriptions). So it would be a huge data waste to store every
product that passes through the catalog, but mostly often never sold.
And even if that were not the case, I want to freeze/capture the
details of each product sold as it pertained to the particular sale,
not risk the accuracy of a past sale by updating the product catalog.
If you want to track inventory, then you can add columns
appropriately, have unique rows in the sales table for each sale,
and decrement the inventory column for every sale.
Inventory is not a consideration here.
it can still capture the price, sell, and desc of each sale because
it is referring to the unique product_id from the products table.
Not if the price changes.
Now, it is likely that you buy two units of a particular soap today
for $2, and sell one for for $2.40 today, and the other for $2.35
tomorrow. In that case, you can move the sell column from the
products table to the sales table.
Exactly. Just a small part of the bigger picture above.
However, you are still identifying the same product from the
products table.
Yes, it is still the same product. It still has the same product_id.
If someone comes in asking for product_id 243, I want to know that's
still the fruity soap, maybe with a different price.
On the other hand, if you buy a soap today for $2, and the same
soap tomorrow for $2.15, then
make it a different product.
But it's the same product, the same soap. I just bought it at a
different price. The 10 I buy today at $2.15 will go in the same
shelf as the 10 I bought last week for $2, behind the shelf label
that reads "Soap. Product_id 243"
See, your buy happens before your sell, so you need to store the
buy price somewhere when you get the item. You can't wait to store
the buy price till you sell it.
Good point, but that doesn't apply here. In this particular case, I'm
not keeping stock. I in fact do only buy when I sell. I only order it
when someone wants to buy it, and it's shipped directly to them (mail
order). But this is getting way off track, and not really relevant to
my SQLite question, though I appreciate your desire to cover the
wider issue.
List out all your application requirements, group all the related
items together so that each unique item occupies one and only one
row in a table, and then refer to that using ids.
Done, as per the tables in my previous post.
So, my question remains, is it possible to update multiple columns
from a single related row in another table, without having to perform
multiple redundant WHERE clauses?
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
