Friday, December 02, 2005

A problem

Word picture of problem.

Clients order jobs.
Companies (valfirms) accept jobs.
A job can be distributed to a number of companies.
A client states a preferred order of companies for a job to be offered to.
A company may accept or reject a job.
If a company accepts a job, it is not offered to any other companies in the queue.
If a company rejects a job, it is offered to the next company in the queue.
A company may accept then DISTRIBUTE a job on behalf of another company:
i. The client sees a job as accepted by the first company, and never knows about the second.
ii. If the second company rejects a job, we go back to the DISTRIBUTE stage.

Code so far:

table queue (client_id, job_id, valfirm_id, sequence, timestamp, allocation_status).


SELECT * FROM queue WHERE allocation_status = 'PENDING';

foreach $result as $job {
//If a job is not accepted already by ANYONE
//If a job is not rejected by ME
//If I am the topmost valfirm, as ordered by SEQUENCE.
SELECT magicsql...
}

Problem with this approach:
27000+ jobs in DB.
Grabbing all that have PENDING somewhere in them means that we have to iterate through 27000 rows, performing 3 checks (anyone, rejected by me, am i topmost?)
Slow!

Better approaches?
Post a Comment