Will having a pseudo-incremental number for bug ID be a problem?

Note that when I say "client", I mean businesses or organizations that have signed up for the service.

I am creating a bug tracking application. I have decided to go with mult开发者_如何学运维i-tenant approach with respect to both the application instance and the database.

Hence, there is one huge bugs table with entries from all clients. The bug ID is an identity specification. Because of this, when any user under any client adds a bug, it gets incremented. For a client who has added just 3 tasks, the task IDs could be #45, #49, #53 because users from other clients may have added some in between!

Is this acceptable from a use-case point of view?

Sometimes clients may take the latest bug ID to be a rough measure of the number of bugs. But in fact it will be the TOTAL bugs in the system. Or they will be plain surprised if their first bug starts from #51134!

On the other hand, if I have this particular ID "behind the scenes", and calculate a "visible" ID for each client separately, they will see the numbers in order. But, when passing a reference bug ID as parameters in URLs I cannot use the user-visible ID because it is not unique. I don't think a ClientID - BugID combo will be elegant. I am afraid using the original identity specification value will cause confusion because the user will see one ID in the UI and another ID in the URL. And no need to say developers will try to use the URL by altering the ID and watch it fail.

How can I solve this issue? I do not want to go to a multi-database approach as I am kind of scared of the maintenance and upgrade process.

I think the principle of least surprise applies here: you need to be consistent with whatever you do. If you are unable to modify the URL scheme then that just leaves non-sequential ID's as the only viable solution. I don't personally see an issue with this, most bug trackers will be able to generate reports of how many bugs were reported in a given period, or how many on a specific project, etc.

On a slightly unrelated note, at work we use a single bug tracking system for all our projects. The system as a whole has a single incrementing ID for bugs in any project. We've never had an issue.

As a general rule of thumb don't show your surrogate keys (IDENTITY values) to your users if you can at all help it. Humans eventually want to change something that they know about so they need not know the primary key values...

The idea about generating a human-consumable identifier would solve your problem best, as you mention, just don't use it like a key in your system. Use your surrogate keys as the keys. (There are usually ways around passing keys in the url string...) Rather, treat your human consumable key as a display field after its initial generation.

Consider concatenating client name abbreviation or client company abbreviation or a portion of the date/time or other counter that you determine with a separate query against context (SELECT MAX(?) FROM q) or a combination these...

Good luck!

One special case I wanted to mention: if this is a public facing website, i.e. public support page or similar, where the customer gives you the support ticket number by phone (i.e. break of the communication medium) then it would be wise to construct an "intelligent" id. For example 5 numbers + checksum. Then the operator (or the system) can more easily check for misread ticket numbers.