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