Hi all,

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.

+3  A: 

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.

Because if I delete a ticket then the sequence will be screwed. :-( Unless I use soft delete...
@Jamie -- why would you delete a ticket? just close it, mark it as superceded, etc. This problem of how to keep numbers in sync will exist regardless of how you calculate the ids.
I would certainly use softdelete on something like tickets, either using the doctrine behavior or just implementing some "deleted" status for the ticket.
@MJB - ah yes, this is very true. Ticket is just an example, I could also use users as an example. i.e. user id 1 for customer 1 and user id 1 for customer 2. And I might delete a user from the system.Perhaps I should stop worrying about what the end user will see in the urls etc.
@Jamie than use softdelete, or create a counter columns in the customer table which gets incremented for every new ticket, but not decremented with the deletion of a ticket.
don't delete tickets, use the a soft delete as mentioned and if you are concerned about what the user will see in terms of the url, either create a rewrite rule for the url or use a uuid for the ticket lookup or some other cryptic measure. keep tickets to help YOU identify what your customers are buying,having problems with or whatever

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.)

This sounds problematic to me. Won't you also need a new version for each customer for every other table? So 10 customers would mean 10 ticket tables, 10 order tables, 10 rma tables, etc? Why not just have a separate database or schema for each customer in that case?
Agree with MJB, if you're the admin for this database, I don't want to switch ;)
Because: Quote from the question: "I'm currently coding a product which will have only one database but will host many clients".It is not that easy as with multiple databases, but it is possible.
+2  A: 

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.

You are correct "each client has his own tickets, and these are not exposed to other clients."Your idea seems very do-able but there is a problem. If for some reason there is a glitch in the database and I get a bizarre ticket number count and then that's added to the ticket ref_num, I will be in a sticky situation. Just thinking of the worst case scenario.
Instead of storing the ticket count, you could use a query to count the number of existing tickets for that user prior to inserting the row into your database, then increment that value by one. It's an additional query on the DB... though it might not be significant depending on the size of your application, frequency of use, and number of users.
Don't count, get the maximum and increment. `SELECT MAX(ref_num) FROM tickets WHERE client_id=?` While this should be equivalent if tickets are never destroyed, you might as well use MAX() to ensure you don't ever duplicate.
+1  A: 

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?!"

+2  A: 

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.

Damir Sudarevic
+1  A: 

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 like this idea, thank you!
+1  A: 

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:



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

Vishal Seth