2

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

  • IMO, a multi-database approach would be better. You can use automated solutions for maintenance and upgrades. For a single database, you have to worry a lot more in your application about data separation. One little bug, and you are exposing other clients' details to a user, which is not likely to be acceptable. – driis May 14 '11 at 13:44
  • 2
    A popular open source issue tracker (redmine) uses sequential ids across all projects in the system. From personal and customer experience I can say it isn't really a problem. I definitely prefer easy integers when looking up a bug and wouldn't want to increase the length of the issue id if possible. – Alex Duggleby May 14 '11 at 13:47
  • @AlexDuggleby: agreed... This is a questionable problem and my answer below is superfluous if the requirement can be eliminated. – Tahbaza May 14 '11 at 13:52
  • @AlexDuggleby: Does it also increment the same id across all customers/clients? How different an impact will it have compared to just using it across all projects of the same customer? –  May 14 '11 at 13:53
  • There is no concept of customers (AFAIK) in Redmine. There are only projects. The users for each customer have rights to specific projects. Across all projects the same ID seed is used, therefore basically yes the ID seed is the same across all customers. – Alex Duggleby May 14 '11 at 13:58
  • Hmm.. I thought I needed client information for billing purposes, but I haven't thought that part through entirely. Anyway, thanks for clearing it up. I guess just a number is enough for users. Doesn't matter if it is in a sequence for their projects alone. –  May 14 '11 at 14:01

3 Answers3

1

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.

Matthew Scharley
  • 127,823
  • 52
  • 194
  • 222
0

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!

Tahbaza
  • 9,486
  • 2
  • 26
  • 39
0

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.

Alex Duggleby
  • 7,948
  • 5
  • 37
  • 44