“To be or not to be”.

You might think, that is the question we ask ourselves.

But in fact, I think, for data base designers, a more appropriate one would be.

Do I select-query this data or do I create an append query?

This is a philosophical discussion. I am interested in knowing what people think about this question.

The example I’m using is as follows:

You create a list of materials for a large project.
The tables include:
Builder
Subdivision
Lot Number
House Type
Options
Details


The detail data is filtered by when it needs to be delivered in the project schedule.
We’ll call that filter “drop”

Now here’s the fun part.
You could:
A. Run a select query to create a purchase order.
B. Run an append query to a separate table.

There are advantages and disadvantages to each. Mostly relating to how you’re going to handle changes that are made to the details tables after a purchase order is generated.

If you run a select query your printed purchase orders (the ones you sent to your vendors) may not match the current snap shot of your details.

If you run an append query you risk leaving details out of the purchasing process.

Either way it ain’t heaven.

Like to hear your thoughts,
Joe

I don't think that data should be duplicated unnecessarily so my first inclination is a select query. However, in most cases it is necessary to be able to reproduce a purchase order at any time in the future. That means that if you change the original data, you have no alternative but to capture the information at the time the purchase order is created and store it in separate tables.

Until the project becomes "real" (as opposed to waiting to be delivered as you describe), you can use a "prototype" query for the "pro-forma" appearance of your P.O. because until it needs to be delivered into your schedule, it doesn't exist anywhere. When the particular piece of the project becomes reality and takes on a life of its own (i.e. becomes a separate entity), you should make it have distinct instantiation (i.e. append query to become separate records).

That way, your records reflect reality. When reality changes, your records change.