A general rule of database design is to seperate reference and
transactional data. Then you can have a normalized database in a
dynamic environment.
T&B 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.
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).
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?
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
