Re: table design question

On 13 Jun 2003 07:02:41 -0700, Nick Davis <ndavis_at_kable.com> wrote:

> 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.

I understand that you want only one order per Customer and Product and Status within a _Period_ of time??

> 1) Is there a better way to setup the table?

I dont' think so.

> 2) How would you suggest ensuring the integrity of the table?

I'd use a row-level-trigger which secures, that there wont be overlapping periods of time with the same Combination of C_ID, P_ID and status-Value.

Hope that helps somewhat.

Cheers,
Friedrich Received on Wed Jun 18 2003 - 22:32:44 CEST