Re: Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

>Richard Huxton <dev(at)archonet(dot)com> wrote on 02/07/2007 01:33:05 AM:
> MargaretGillon(at)chromalloy(dot)com wrote:
> > I am wondering what the limit is on the number of joins in a View or a

> > Select.
> >
> > Background: I have many tables with similar flags such as Active,
> > Inactive, High, Medium, Low. I am storing the flags in a flag table
and
> > then putting an int4 foreign key to the flag in the data tables. Some
data
> > tables may have up to 15 flags, as well as 30 or 40 other foreign
keys.
> > They're all left outer joins. Is this a problem for a view?
>
> No real problem, but I suspect you'd be better off with a simpler setup:
>
> CREATE TABLE has_some_flags(
> ...
> priority_flag char,
> ...
> CONSTRAINT valid_priority_flag CHECK
> (priority_flag IN ('A','I','H','M','L'))
> )
>
> Or, perhaps better for your particular case:
>
> CREATE DOMAIN priority_flag char
> CONSTRAINT valid_pri_flag CHECK (VALUE IN ('A','I','H','M','L'));
> CREATE TABLE has_flags(pri_flag priority_flag);
>
> The main question would be whether your flags are going to change - if
> not, they're more like a type and use the CHECK constraint. On the other

> hand, if you're updating them regularly then you'll want to use joins.
> --
> Richard Huxton
> Archonet Ltd

I was using the flag table to keep the flags consistent between all the
tables in the database that might use them. I didn't know about CREATE
DOMAIN which will do what I want perfectly. Thank you.
Margaret Gillon.