One database, multiple customers

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 开发者_StackOverflow中文版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.

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.

Am I to understand that each client will have their tickets listed sequentially from 1 onwards? I will assume that each client has his own tickets, and these are not exposed to other clients. You should clarify this in your question.

I would suggest creating a new column in your ticket table to use as a reference number (lets call it ref_num). When client 2 creates a ticket, the ticket_id will be 5, but your application will know to assign the ref_num to 1 for that user.

In order for that to work properly, you will need to find a way to track the number of tickets created by each client. Perhaps for each contact you could have a column called ticket_count that increments by one each time. That way, from within your application, you can just pull this number and increment it by one to update your count and get the new ticket number.

FYI, over here we do not worry about hiding information from the user. We use a ref_num field to hide true ID numbers from the users. We have multiple departments, each of which have multiple clients, that share the same ID pool. If Group A creates ticket 23456 then group B creates another ticket, it appears as 23457 despite the fact that the previous ticket did not belong to them. The ticket number does not need to be sequential.

Another possibility is to NOT use consecutive integer ticket ids. You could timestamp the tickets, perhaps even down to the second, and use that. Certain vendors I work with do the same. Other folks keep track of the count and append that to the timestamp, which has all the appearance of being a unique number with no gaps, but might not in actuality be implemented that way.

In that case, the customer sees a ticket number like 2010062407 and thinks, "clearly that is related to the day, maybe even the time". But I don't think the customer would say, "How did I rack up 2 billion trouble tickets?!"

It looks to me that you are confusing concept of a primary key and a business key. Function of a primary key is to uniquely identify a row in a table, nothing else. Whenever one tries to attach a meaning to primary keys -- something is wrong with the initial design.

If a ticket number has some business meaning -- and expected sequence -- simply have a sequence generator for each of your customers and add a column for that.

Create a new table called CustomerTickets, which has two columns customer id and ticket id. For your customer read the last ticket id from the table for your customer id, increment it and then write it into your actual ticket table all rolled up in a transaction.

I'd say you should keep the Primary ID the way it is, logically its supposed to be internal. You may generate a unique identifier (GUID, timestamp) and may use it for display purposes. Alternatively, you may generate a unique ID (for display purpose) combining first, say 4, letters of customer name and then padding it with 0s.

something like:

APPL000006

GOOG000004

use that, trim first four letters and then you get your primary ID back.

You also could try to create extra tables per customer, so you end up with new tables for each customer.
E.g. Tables:

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

(If you do not have thousands of customers this should not be a problem.)