Rows in tblinvoiceitems where invoiceid does not exist in tblinvoices?

[Rendrag]

Hi Folks,

 

We had a failed merge from another hosting provider who we bought out, over the weekend. Turned out we tried to import from a 2-year old copy of their database, did not go well at all (Can we get some error checking added to the merge functionality? Or even if you don't have time, can we get a non-encrypted copy of the merge script, and we'll add that for you? (Happy to sign an NDA if you like - we have at a few more of these to do this year, with > 1500 customers on each, so the boss has ok'd spending some time making the merge process more robust!)

 

Anyway, I got that cleaned up, and re-imported from the correct database, and everything is going nicely now. Only issue is, that while cleaning up, I discovered a weird anomaly, which I'm not sure is by design, or just something has gone hinky over the years.

 

select count(*) from tblinvoiceitems where not exists (select id from tblinvoices where id=tblinvoiceitems.invoiceid);
# count(*)
'2760'

 

There's 2760 rows in tblinvoiceitems, with an invoiceid which does not exist in the tblinvoices table. Is that a data error (aka should I be deleting those rows), or is it by design (are they billable items awaiting invoicing or something?). The id's of the invoiceitems are pretty consistently spread from the start of our WHMCS installation up to present day

 

As we pull data from these tables into our customer portal, I'd really like get a definitive answer on what these rows are for, if anything

 

Thanks,

 

Damien

[WHMCS John]

Hi,

If an invoice was deleted the record in tblinvoiceitems remains by design. This is how WHMCS knows not to generate a duplicate invoice for a service on a particular next due date - even if the original invoice was deleted.

 

Leaving them would be totally normal, but if you wanted to delete them I don't see it causing a major problem.

[Rendrag]

Thanks John, that answers a different question I had also Thanks for your help!