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

Richard,
I have a few questions on the domain.

1) How do I add a domain to an existing table? Can I add it to an existing
column or do I need to make a new column with the domain and copy the
existing data into it?

2) What happens to the domain and tables using it if I have to modify the
domain?

Margaret Gillon