One database, multiple customers

Question

I'm currently coding a product which will have only one database but will host many clients by using a global identifier of a customer_id.

This is all very good.

However, let's say we have a table called Ticket. Idea has a primary key. When a user adds a ticket, the ticket will correspond to the customer via a foreign key etc.

However, for each customer I want their ticket ids to start from 1 when they sign up.

i.e. customer 1 adds 4 tickets, the ticket_id count will be 4. Customer 2 signs up, they add a ticket and then the ticket_id will be 5 and so on and so forth. Which is not ideal.

My question to you is, how do I get around this if I don't want to use multiple databases? The idea of having to update tend, if hundreds of databases with new columns, indexes etc. whenever I make a change would irritate me hugely.

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

EDIT: Tagged as symfony as I will be using Doctrine ORM in symfony to manage the database.... probably irrelevant, but added just in case.

EDIT: I might also be being stupid and missing something obvious here, so my apologies.

Answer 1

Why not just add a ticket_num column, and manage keeping them sequential-per-customer-id yourself? If you're using doctrine, look into impelementing a preInsert method in your Ticket model.