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

MargaretGillon@chromalloy.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