I have been writing some queries to read my database eg. a list of clients and their email address. To do this I’ve always (so far) done a join where clients.id = contacts.id which has worked correctly.
Today I got some incorrect results and on investigation found that the consistent key between client and contact was actually public_id.
Looking in the DB I see the ids in lock step until the last few clients:
cl.id pub Name co.id pub Name
172 172 Fred 172 172 Fred (all prior clients id’s match)
174 173 Jane 173 173 Jane
175 174 Bill 174 174 Bill
So – seems client.id 173 was skipped. It’s quite possible that a client was deleted by my son during his setup of data.
The system when viewed from the application is totally consistent but I’d really appreciate confirmation if this state is ok to run with or indicative of an issue in the db.