table design question

Hi,

I am new to this group.
Please let me know if my question is inappropriate for this group.

We are creating a table that will hold recurring orders. Each order record will have a "customer ID", "product ID", "quantity", "effective start date", "effective end date" and a "status". The "status" may be "active", "inactive" or "special".

When an order is shipped to the customer, the quantity to ship would be determined as follows:
First, search for a record with a status of "special" and a date that falls between the "effective start date", "effective end date". If no specials are found, then search for a record with a status of "active" and the same date requirements.

There should only be one active order for a customer on a specified date. The same should apply to special orders.

So my questions are:

1. Is there a better way to setup the table?
2. How would you suggest ensuring the integrity of the table?

Cheers,
Nick Received on Fri Jun 13 2003 - 09:02:41 CDT