At 06:45 AM 03-02-02 +0000, you wrote:

>Are there any drawbacks to sharing a generator between several tables
>for PK generation? I figured that if I have a bunch of low-turnover
>tables, I can have them all use the same gen (e.g. GEN_SHARED) for
>their PK field.

No, none. It's a good idea.

>The only thing I can think of is the potential large jumps in a
>table's PK values, e.g.
>
> TABLE A
>PK
>---
>3
>4
>5
>455
>456
>457
>
>which is just an aesthetic consideration with no practical
>implication, as far as I can tell (it's like an exaggerated version
>of the gaps resulting from rolling back after a gen_id() call).

Yes; if there are "aesthetic considerations" in numbering sequence of values in generator columns (for example, for serial or document numbering) then you need to do a fair old bit of background work to maintain an auditable sequence. (There's a paper on the topic amongst the IBO TechInfo collection).

As for keys, all that's wanted is for them to be atomic and (if primary), unique.

>One minor benefit of using a shared gen would be that you would know
>not only the order that the records were created in each table, but
>the order in which all records in all tables sharing the gen were
>created. I'm pressed to find a reason where this could be of use,
>but it might come in handy.

Nah, you don't have to search around for reasons to justify sharing a generator among low-dose tables. :)

>Is sharing gens a common or bad practice? If this is a bad idea, I'd
>prefer to find out now before I implement it.

Common, likely to become more so now that we have 64-bit generators...

>Also, I recall reading something about a limit to the number of
>generators you can have in a database (127 is the number that sticks
>in my mind) ...?

There is some sort of limit in IB, relating to the fact that there is only one page in any db for storing generators. It was fixed in Firebird yonks ago. (Ann's the one who can do the magic numbers on these things...)

cheers,
H.

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________