Hi everyone!

I'm running into a little problem and was wondering if some of you could suggest a good way to accomplish this.

Here is what I'm doing:

I have a database that supports multiple "Systems" that can each create their own reports.

(Think asp.net membership system) "many application in one database"

"System table" one to many "Reports table"

In my reports table I have an auto-number ID field. The problem is I would like to have an auto-number for each system, not all of them and still keep the auto-number ID as the primary key. So right now it works like this

Report

System ID

System One 1 System One 2 System Two 3 System One 4 System Two 5 System One 6

but I would like it like this:

Report

System ReportID ID

System One SO001 1 System One SO002 2 System Two ST001 3 System One SO003 4 System Two ST002 5 System One SO004 6

how do some of you handle report numbers for multiple systems in one database so you get a nice sequential, logical report number?

Thanks!

get a nice sequential, logical report number?

I can't help you,I gave up on this long ago. Now I consider primary keys that have no intrinsic meaning to be a good thing.

I agree with Arschr, just use the artificial number to be what a PK is for: identifying the row. Having non-unique numbers means it's really not a PK value.

Using these kind of sequence numbers in a DB is not really possible without a lot of pain (table locks, risk of having gaps in the sequence etc.)

Otis wrote:

I agree with Arschr, just use the artificial number to be what a PK is for: identifying the row. Having non-unique numbers means it's really not a PK value.

Using these kind of sequence numbers in a DB is not really possible without a lot of pain (table locks, risk of having gaps in the sequence etc.)

Yeah, I acctually do agree with both of you too. But I'm running into problems with the audience. They want a meaningful report number for them. Maybe I can display a combination of the primary key and the "system id" so it seems more "meaningful" to them. I know that I shouldn't even be displaying a primary key at all to the user. This just seems like a terrible idea. Maybe a unique constraint would be more appropriate? thanks Frans and arschr!

It's not uncommon to see an hidden PK and a meaningful reference identifier in two distinct columns of a "Products" or "Customers" table...

In case you really need consecutive identifiers, before inserting a new record you'll have to first retrieve the last identifier using some kind of SELECT MAX() query, increment it by one and finally you'll be ready to send the INSERT statement. Of course, there's a risk of two concurrent transactions generating the same identifier if they occur at the same time so you'll have to chose an appropriate isolation level (repeatable read or serializable should be fine).

stefcl wrote:

It's not uncommon to see an hidden PK and a meaningful reference identifier in two distinct columns of a "Products" or "Customers" table...

In case you really need consecutive identifiers, before inserting a new record you'll have to first retrieve the last identifier using some kind of SELECT MAX() query, increment it by one and finally you'll be ready to send the INSERT statement. Of course, there's a risk of two concurrent transactions generating the same identifier if they occur at the same time so you'll have to chose an appropriate isolation level (repeatable read or serializable should be fine).

that's not a bad solution. I don't care (and neither do the audience) a whole lot about the sequential number being perfectly in order, as long as it's unique.

Sometimes when I set up a new system the user notices the primary key as the report number they ALWAYS ask, "hey, why are my reports starting at 2390?", or something like that. I should have had a unique contstraint. I HATE that I exposed the pkey that way, but such is life.

I'm in the process of refactoring the application and would like to make that portion of the database and reporting, a lot better. Thanks!