ok, your problem makes a bit more sense, but still, much in it doesn't
make sense.
On 6/18/07, T&B <[EMAIL PROTECTED]> wrote:
Hi Puneet and John,
You each respectively said:
> Why are you repeating the Code, Buy, Sell, and Description columns
> in the Sale_Products table when they already exists in the Products
> table?
> A traditional goal in database design is to place data in "Third
> Normal Form" which means in essence that each data element is only
> stored in one place
Good question/point. Sorry I thought from my introverted world that
this would be obvious, so didn't elaborate. So let me clarify:
The Products table is changing all the time. What might be listed
today when a Sale is made, might no longer be listed in Products in a
couple of weeks (but needs to retain the details in the Sale). And
even if it is still listed in Products, it's very likely that the
price and probably description will have changed.
even if the products table is changing, the buy, sell, and description
of a given item shouldn't change. Further, even if buy and sell
change, then at least the description will be not be different. 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, and one of the basic
rules of normalization is that all related things should be together.
So, all information about a product should be in the products table,
not in the sale_products table. 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.
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.
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.
So the Sale_Products table needs to capture the values of the Buy
Price, Sell Price and Description (and others) when the sale is made,
and cannot simply be dynamically related to their occurrence in the
Products table (since it will change, but the Sale_Products extract
for the current sale must remain unchanged).
right... 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. 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. However, you are still identifying
the same product from the products table. 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. 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.
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.
> assume that the "buy" column is the price I paid for the item, and
> "sell" column is the price I get for it) --
Yes, that's right.
> CREATE TABLE products (
> product_id INTEGER PRIMARY KEY,
> buy REAL,
> sell REAL,
> desc TEXT
> );
>
> CREATE TABLE customers (
> customer_id INTEGER PRIMARY KEY,
> .. other customer info columns ..
> );
>
> CREATE TABLE sales (
> sale_id INTEGER PRIMARY KEY,
> product_id INTEGER, -- FK to products table
> customer_id INTEGER -- FK to customes table
> );
One sale may involve several products, so it's more like this:
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
product_id INTEGER, -- FK to products table
customer_id INTEGER -- FK to customes table
);
CREATE TABLE sale_products (
sale_id INTEGER, -- FK to sales table
product_id INTEGER, -- FK to products table
buy REAL,
sell REAL,
desc TEXT
);
Now, when a new product is added to a Sale, I do this:
INSERT INTO sale_products(sale_id, product_id) VALUES( ? , ? )
And what I need is some kind of trigger that will automatically fill
in the buy, sell and desc columns for me.
Something like:
CERATE TRIGGER update_sale_products
AFTER UPDATE OF product_id
on sale_products
BEGIN
UPDATE sale_products
SET
buy = (SELECT buy FROM products WHERE products.product_id =
NEW.product_id)
, sell = (SELECT sell FROM products WHERE products.product_id =
NEW.product_id)
, desc = (SELECT desc FROM products WHERE products.product_id =
NEW.product_id)
WHERE
ROWID=NEW.ROWID
;
END
which is basically just a rewrite of my original post, but using your
capitalization and entity names.
But I want something without the multiple lookups on the products
table of the same thing, ie the:
WHERE products.product_id = NEW.product_id
Any ideas?
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
S&T Policy Fellow, National Academy of Sciences http://www.nas.edu/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
