Auto incrementing a column based on another I have an application which generates invoice_id's. These id's need to be unique. The issue is that a client can have more than one invoice depending on what plan they are on. It could be 2, 3 or 4. So I made a column with extra invoice_id's which I called invoice_idA and invoice_idB. 

Now I do not know how to handle the incrementing of the invoice_id. It would be easier if it is one invoice so I can just use an auto_increment but I will have an issue because if there is more than one invoice_id in a column for one client, there could be duplicates.

Looking at the picture,


I need a way to make invoice_id increment to 1008 based on the value in invoice_idA of 1007.
If I insert a value in invoice_idB like 1008, how will I increment invoice_id to read from it because it could be 2,3 or 4 extra invoice numbers. I believe even if there was a way I may need to know the exact column to increment from but in this case, it could be invoice_idA or invoice_idB.
romeo, before I craft a reply, I want to ask: Is there any chance you have the opportunity to edit the schema in this case?

If you can, you definitely should, because the problems you're running into are because of denormalization.

Normalization of the schema

From your example table above, it appears this should be split into at least two separate tables. From the information you've given you should have an invoice table and a client table, with (pseudocode) definitions as

CREATE TABLE invoice (invoice_id, client_id, invoice_date);


and

CREATE TABLE customer (client_id,service_id, phone_number);


Using these tables, you can establish a foreign key relationship from invoice (client_id) to customer (client_id), allowing a many-to-one relationship between invoice and client.

Subsequently, you can obtain all invoices pertaining to a particular client via

SELECT * FROM invoice JOIN client ON invoice.client_id = clinet.client_id
WHERE client = ...


Hope this helps!