--- In firebird-support@yahoogroups.com, "Michael Vilhelmsen"
<mivi@m...> wrote:

>
> Hi
>
> After this years conference in Prague (great conference BTW) I'm
> trying to optimize our database. And in some extend normalize it.
>
> But doing this have raised some questions.
> I'll just take them from top down.
>
>
> 1.
> I have a table.
> This table is defined as:
>
> CREATE TABLE VAREFRVSTR (
> VAREPLU_ID VARCHAR(30) NOT NULL,
> FARVE_NAVN VARCHAR(30) NOT NULL,
> LAENGDE_NAVN VARCHAR(30) NOT NULL,
> STOERRELSE_NAVN VARCHAR(30) NOT NULL,
> V509INDEX VARCHAR(30) DEFAULT ''
> ... some more fields
> );
>
> At this point the PK is
>
> ALTER TABLE VAREFRVSTR ADD PRIMARY KEY (VAREPLU_ID, FARVE_NAVN,
> LAENGDE_NAVN, STOERRELSE_NAVN);
>
> So its actually build from 4 varchar(30) fields.
>
> Would I benefit from adding a new field called ID which is an integer,
> and have this updated via a trigger and a generator ?
>

Definately

IMO, the primary key of a table should not have any "real-world"
relevance outside the database. Having a single field surrogate PK
allows you to more easily create foreign key relationships with other
tables.

As far as updating by a generator, once you have assigned the ID, it
should never be changed (again IMO). If there is no real world
relevance, no-one cares what value it has anyway.

To maintain backwards compatibility, you can do something like this in
a before insert trigger.

if (NEW.ID is null) then
begin
NEW.ID = gen_id(mygenerator,1);
end

>
> 2.
> Can I drop a PK if it isn't being referenced anywhere ?

Yes, but chances are that super-key still needs to be declared as
unique, and probably still needs the fields to be indexed. Using
triggers, you can make the database totally compatible with existing
programs, although if you have used select *, or insert into without
declaring field names it may get confused.

>
> 3.
> If my PK has references, should I then change the table that
> references this PK to reference my new PK instead ?
>

How are you referencing a multiple field key? Anyway yes.

>
>
> 4.
> Ann talked about optimizing the DB in Praque.
> She talked something about an index shouldn't have to many dublicate
> values.
> This I understood, and are about to drop some index (also that an
> index is only use to retreive records. Not sorting).
>

Not sure about this. IIRC, an index can be used in sorting, but won't
always be. The issue with lots of duplicates is mainly a problem with
garbage collection, and a lot of this is resolved in Firebird 2 (now
in beta). You can also avoid using the poor indices in a selection by
adding 0.

join someothertable b on (a.reallypoorselectivity+0 = b.)

etc

> But a FK can be seen as an "Index".
> This can sometimes have a bad selectivity.
> So should this FK be redefined ?
>

In FB 1.5, if you are experiencing trouble the only real alternative
is to drop the FK definition, and to maintain the relationship using
triggers. You can then add the PK to the end of the FK field in a new
index and it is then unique.

Adam