I want to ask a simple question: I am doing a database project which
involves a lot of tables, in which two are product table and order
item table.

Products are selected from the product table when an order item is
created in the order item table. However this brings a problem when
some of the product details needs to change daily. Doing this will
change old order item details as the two tables are just related by
product ID.

I have heard two solving approaches:

One is to decrease the relations of the two tables, when the product
is selected from the product table, just copy all the information
required by the order item table.

The second is to set a flag in the product table to disable it and
create another product record for new order items. However, the
disadvantage of this method is bring a little trouble to the user to
create new product record.

I want to ask what is the most usual method to implement the product
changing?

terry wrote:
This would entail having the same data multible times, one for each order
using the same productinformation. Wasteful, at best.

This is what I would do. Mind you, I wouldn't generate a new productnumber,
just mark each record with a timestamp.

Just to add to the confusion, ask yourself the question: Would it be
possible to update the data retroactively, i.e. "As of yestermorn the
details of this product was changed, but we just didn't get around to
updating the database until now." If yes, which version of the product
detail do you want the order created in the meantime to refer to?

Datetime issues in database can be very complex.

--
Kristian Damm Jensen damm (at) ofir (dot) dk
To announce that there must be no criticism of the President, or that
we are to stand by the President, right or wrong, is not only
unpatriotic and servile, but is morally treasonable to the American
public. -- Theodore Roosevelt

leonlai2k (AT) yahoo (DOT) com (terry) wrote in message news:<9904d48.0402180355.74979423 (AT) posting (DOT) google.com>...
choke!
This is how you do it in a flatfile type system. If you are using a
DB, use it right.
This is slightly better.
Here is your problem: asking the wrong question. Rather than trying to
find out how others met their design requirements, you should ask how
different data models fit to YOUR requirements.

What features and functionality does your application require? For
example,

how long must you retain order information? Hours? Years? (This time
period may be affected by your company needs and possibly by law.)

must you support obsolete products? (If you need to retain Order
information for long periods of time, you may need to retain product
information at least as long)

are there auditting issues involved? such as, do you need to be able
to document order or product changes over time? (and maybe even who
made the changes?)

From what I see, you are looking to start implementing this before you
even know what features you need. If you are building a prototype, I'd
suggest using timestamps (start and end times) instead of a flag. You
need to research your needs first before going much further.

HTH,
Ed

"terry" <leonlai2k (AT) yahoo (DOT) com> wrote

You are conflating two things. One is historical data, and the other is
current data.

An historical record of a product is different from the current record of a
product. Different things. Different entities. Different relations.

What does that suggest to you?

CREATE TABLE ProductHistory
(upc CHAR(10) NOT NULL
price DECMAL(12,4) NOT NULL,
...
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date TIMESTAMP, -- null means current
PRIMARY KEY (upc, start_date));

and it is handy to have:

CREATE VIEW CurrentProducts (...)
AS SELECT ..., COALESCE(end_date, CURRENT_TIMESTAMP)
FROM ProductHistory;

Now use a "order_date BETWEEN start_date AND COALESCE(end_date,
CURRENT_TIMESTAMP)" predicate to pick the right price and whatever
else you need.