One database, multiple clients

Question

I am currently encoding a product that will have only one database, but will contain many clients using the global identifier customer_id.

All this is very good.

However, let's say we have a Ticket table. An idea has a primary key. When a user adds a ticket, the ticket will match the customer with a foreign key, etc.

However, for each client, I want their ticket IDs to begin with 1 when they sign.

i.e. client 1 adds 4 tickets, the ticket_id counter will be 4. Client 2 signs up, they add a ticket, and then ticket_id will be 5 and so on and so forth. Which is not perfect.

My question to you is: how do I get around this if I do not want to use multiple databases? The idea of ​​updating requires hundreds of databases with new columns, indexes, etc. Whenever I made changes, I would be very annoyed.

Hope this makes sense and I look forward to your input.

EDIT: tagged symfony, since I will use Doctrine ORM in symfony to manage the database .... probably doesn't matter, but added just in case.

EDIT: I can also be stupid and miss something obvious here, so my apologies.

Answer 1

, , 1 ? , , . .

( ref_num). 2 , ticket_id 5, , ref_num 1 .

, , . , ticket_count, . , , .

FYI, , . ref_num, . , , . 23456, B , 23457, , . .

Answer 2

ticket_num ? , preInsert Ticket.

Answer 3

- . , , , . , , . , , .

, 2010062407, , ", , , ". , : " 2 ?"

Answer 4

, -. , , . , - - - .

- - - .

Answer 5

CustomerTickets, . , , , .

Answer 6

, Primary ID , , . (GUID, ) . ( ), , 4, , 0 .

- :

APPL000006

GOOG000004

use this, crop the first four letters, and then return your primary identifier.

Answer 7

You can also try to create additional tables for each client, so that you get new tables for each client.
For instance. Tables:

tickets_1 //all tickets for customer with id 1
tickets_3 //all tickets for customer with id 3

(Unless you have thousands of customers, this should not be a problem.)