CODEWITHSUNDEEP
phpmysqldatabase-design
user254875486
user254875486

Reputation: 11240

Is redundant data always a no-go

I'm working on a shop-like system (in PHP & MySQL) that exports invoices to an external system. The proces goes something like this;

For every XML that's being sent to the external server a record is created in the database containing the corresponding invoicenumber and a status (status is initially SENT, indicating that the XML is sent.) After the system has handled the response the status is either SUCCESS or ERROR. Now, the problem is; at some point I want to fetch a list of invoices that have no record in the request table with status SUCCESS.

EDIT: If the status is ERROR there will be a new request for the same invoice, so there is a possibility that there will be more than one request per invoice.

My order table has columns ID and InvoiceNumber, and the request table has columns ID, InvoiceNumber and Status, so to get the list mentioned I could do something like:

SELECT InvoiceNumber 
FROM orders AS a
LEFT JOIN requests AS b
    ON a.InvoiceNumber = b.InvoiceNumber
WHERE NOT EXISTS (SELECT ID 
                       FROM requests 
                       WHERE status = "SUCCESS" 
                           AND request.InvoiceNumber = a.InvoiceNumber)

However, a second option would be to create an extra column to the order table (ie. requestSucces) being initially 0 and set to 1 if the system handles a successful response for the corresponding invoice. This would result in a much easier and less expensive query to get the list of invoices that need to be (re)sent (SELECT invoiceNumber FROM orders WHERE requestSuccess = 0), however the field would technically be redundant.

My question is: what would be better; use the easy query at the cost of having a redundant field or use the heavier query and not pollute the database with redundancy. Of course; if any of you know of a better solution without using redundancy that would be even better.

Upvotes: 3

Views: 164

Answers (3)

liquorvicar
liquorvicar

Reputation: 6106

Given that from the info you provide it seems there will always be at least one entry in your Requests table for each Invoice you could get the data you need from a query like this:

SELECT InvoiceNumber
FROM Requests AS r_error
LEFT JOIN Requests AS r_success
ON (r_error.InvoiceNumber=r_success.InvoiceNumber AND r_error.status!='SUCCESS'
    AND r_success.status='SUCCESS')
WHERE r_success.InvoiceNumber IS NULL

I'm not sure it would be lightning fast but I think it would be quicker than your initial query (I haven't tested that theory!) but more importantly it doesn't use your Orders table so shouldn't impact your transaction processing as much.

Upvotes: 0

Iggy Van Der Wielen
Iggy Van Der Wielen

Reputation: 124

when you make the status field numeric error=0 and succes=1 one you could do a max status grouped by invoicenumber to see which invoices are no

Upvotes: 2

Patches
Patches

Reputation: 1125

Wars have been fought over normalization :-)

But:
Since you're looking for invoices that are not set to success, couldn't you do this? 

SELECT InvoiceNumber
FROM requests 
WHERE status != "SUCCESS"

(Note the not equal symbol)

Upvotes: 0

Related Questions