On 5/3/2010 3:34 AM, Michael Ludwig wrote:

>
> What about the PRIMARY KEY( a, b ) ? It looks okay to me, but I'm
> reading in Helen's book (on p.274) advice against compound primary
> keys in Firebird. For those who have the book, does any of it apply
> here?
>

I think her caution relates to long compound keys (that people sometimes
create for other RDBMS). Creating a single PRIMARY KEY (A,B) is fine.
Firebird will use it when it knows the A value so there is no need for a
separate index on A. Having that primary key also gets you a unique
constraint on A+B as an important by product.
You can then add just the index on B which would be useful for
searching/joining when only the B value was known.

With just these two I'd stop with keys and indexes and see how
everything performs. Since A and B values are both from the primary key
of the users table the selectivity will be very good and I would not
anticipate any problems at all. Good lean Firebird design.

> I could certainly add an FS.id BIGNUM as a surrogate key, but somehow
> it doesn't look better than the two USR.id INTEGER to me. In fact, it
> looks like overhead in this particular case - but my thinking might be
> flawed, or not firebirdish enough.
>

Your thinking is good! No need for it.

Surrogate keys are best as replacements for those long compound keys
mentioned earlier. You do not have those so I would advise keeping it
simpler without a surrogate key.

Looks like you are well on your way to a spiffy system.