It is never a bad idea to have a primary key!

What you should try to avoid is indexes resulting in lots of
duplicates. This is the reason why having foreign keys on lookup
tables sometimes is discouraged - such an index would not matter for
the lookup table, but the table referencing the lookup table would
have lots of duplicate entries slowing down at least updates, deletes
and selects.

A selectivity of 0,16 is not bad if your table contains 6 records. On
the other hand, if your table contains 100 million records, then even
a selectivity of 0,01 is lousy and you should not create any index
referencing a lookup table with 100 elements (1 divided by number of
records in table is the ideal selectivity).

Where to put an absolute limit for whether you should create a foreign
key or not? Well, I haven't got a clue and I think answers to your
question will vary according to the size/complexity of
tables/databases that people are accustomed to work with.

Set

--- In firebird-support@yahoogroups.com, Alexander Tabakov <saho@e...>
wrote:

> Hello guys,
>
> I have some questions about indexes.
>
> From discussions in this list we know that creating FK on a field
> with low selectivity (e.g. a lookup table) is a bad thing. That's
> OK.
>
> But, what about the lookup table itself? Do you think we must skip
> the PK on the lookup table either (I am not very happy with this).
>
> What is the reasonable number of rows after which we can safely
> create a FK to reference the lookup. For example we have a hierarchy
> with about 100 elements. Is it safe to create a FK referencing this
> hierarchy?
>
> Generally how do I know that a selectivity is OK. I have an index in
> another table with six rows each one with different row ID and the
> selectivity is 0,1666.. is it bad or good to have this index?