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

[MargaretGillon@xxxxxxxxxxxxxx]

>Richard Huxton <dev@xxxxxxxxxxxx> wrote
on 02/07/2007 01:33:05 AM:
> MargaretGillon@xxxxxxxxxxxxxx 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.