View Full Version : Generate Numbers

08-25-2007, 04:38 PM

I hope someone can help me out.

I have a table which stores client information. In this table I also need to store a field called case_number.

My client would like the case_number to be automatically generated when she adds a new client. And, she would like the case numbers to be sequential.

So if I have ClientA in a table with case number 2020 and I add ClientB to the table, I would like their case number to be 2021. And, if I add ClientC to the table, their case number sould be 2022. Etc.

Is it possible to write a script to generate a number in sequential order and store that number into a table?

If so, how do I go about scripting something like this?

Thank you in advance.

08-25-2007, 04:47 PM
set it to auto_increment.

08-25-2007, 04:48 PM
Can I have two auto_increment fields in a table? I tried that one time and I got an error.

08-25-2007, 04:51 PM
you should be able to. i dont see why not

08-25-2007, 04:56 PM

I gave it a quick try. When I went to save the table, I received the following message:
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

I already have a primary key set to auto_increment. I need to have another column which increments numbers as well and it will need to increment from a starting number of 2000.

08-25-2007, 05:22 PM
Two questions - Do clients and case numbers always have a one to one relationship (is a repeat client considered to be a different client because it will have a different case number, this is what your description implies)? Can clients have more than one case number (ClientA, case numbers 2002, 2040, 2140...)?

If the answer to question one is yes, then use your existing primary key as the client ID and as the case number and set it to a starting value of 2000.

If the answer to question two is yes, you need two tables, one to hold the client information and one to hold the case information and have a column in the case table with the corresponding client ID.

08-25-2007, 05:35 PM

A client can be a repeat client. If the client is a repeat client, then he/she will have a new case number assigned. Therefore, a client will never have more than one case number assigned to them.

Let me see if I'm understanding your suggestion. My table currently looks like this:

client_id | name
1 | joe
2 | mary

I need to change my table to look like this:
client_id | name | case_no
1 | joe | 2000
2 |mary | 2001

Are you suggesting that my table should actually look like this?
client_id | name | case_no
2000 | joe | 2000
2001 | mary | 2001

Where the client_id is auto_incremented starting at 2000 and then I do a mysql_insert_id into the case_no field? So now client_id and case_no are the same?

Am I on the right track here?

08-25-2007, 06:17 PM
This part makes sense: "A client can be a repeat client. If the client is a repeat client, then he/she will have a new case number assigned."

This part contradicts the first part: "Therefore, a client will never have more than one case number assigned to them."

By stating that a repeat client will have a new case number assigned, that client will then have more than one case number in the database (even if a previous case is marked as being closed/completed/dormant...)

This is what you need to achieve -

Client table:
client_id | client detail columns (name | address | phone | ...)
1 | joe
2 | mary

Case table:
case_no | client_id | case detail columns (start, end, status... whatever you need)
2000 | 1 | details...
2001 | 2 | details...
2020 | 1 | details... // joe is a repeat customer with a new case and a new case number, but he already exists as a client in the client table

One thing to remember with database design is don't duplicate information. Store each piece of information only once.

I suspect that each case could have multiple log entries/notes/expenses... or something similar? You would make one more table to hold them -

Case log table:
log_no | case_no | date | type (whatever you need) | details... (whatever you need)
1 | 2000 | 2007-08-25 | note | "held initial meeting with client"
2 | 2000 | 2007-08-25 | business meal expense | $55.00
3 | 2020 | 2008-08-25 | note | "met with an old client about a new case"

08-25-2007, 06:24 PM

I see what you are saying. After reading my statement again, I realized that I missed a bit of info.

If a client becomes a repeat client, he/she will be assigned a new client_id number as well as a new case_no.

For whatever reason, in this particular case, probably for regulatory reasons, they need to treat everyone as new since the situation may be different each time.

08-25-2007, 07:38 PM
If you're starting from a new table with no data in it client_id & case_no should be the same using auto_increment. Although whether its the best idea or not i dont know.

This code should work for the 2 auto_increments.

mysql_query("CREATE TABLE example (
client_id INT auto_increment NOT NULL,
case_no INT auto_increment NOT NULL,
primary key(client_id),
primary key(case_no)