Posted by petersprc on 10/11/06 15:47

Perhaps this could work: If you keep the submissions in a set of
staging tables, then your unique key would work. In this scenario, you
wouldn't delete the submissions after they've been processed. Keep them
around to check uniqueness of future submissions. You might be able to
add a bit to the staging table (submission.committed) which would let
you select the new items only and pass them along to ZC.

Of the things you're considering, a single query would be more
scalable, but it's like you won't notice much difference at all until
the volume went up.

Alan Little wrote:
> I have affiliates submitting batches of anywhere from 10 to several
> hundred orders. Each order in the batch must include an order ID,
> originated by the affiliate, which must be unique across all orders in
> all batches ever submitted by that affiliate. I'm trying to figure out
> the most efficient way to check the uniqueness of the order ID.
>
> Order data is being submitted to Zen Cart, and also stored in custom
> tables. I have created a unique key in the custom tables, consisting of
> affiliate ID and order ID, but that's not workable because the custom
> tables need data from ZC, and I don't really want to submit the data to
> ZC until I've checked the order IDs, plus part of the batch might already
> be stored before I hit a dup, and would then have to be deleted.
>
> There are currently about a half dozen affiliates; I have no idea how
> many there will be ultimately. The client I'm developing this for thinks
> 1-200. Each affiliate can potentially have several thousand order IDs
> that need to be checked.
>
> The two ways I'm considering are: 1) a single query to load all the
> affiliate's order IDs into an array for checking as I process the batch;
> 2) creating a temporary table with just the affiliate's IDs, querying it
> for each order in the batch.
>
> Any ideas which would be more efficient? Would there be any significant
> difference either way?
>
> --
> Alan Little
> Phorm PHP Form Processor
> http://www.phorm.com/

[Back to original message]