Re: Design question regarding data typing

From: Bob Hairgrove <>
Date: Sat, 25 Feb 2006 12:57:36 +0100
Message-ID: <>

On 24 Feb 2006 14:50:48 -0800, "Jacob JKW" <> wrote:

[some snipped]

>I guess the point I'm clumsily trying to make is that the building_type
>attribuite isn't there to avoid the JOIN when I'm trying to get
>specialized data on a single building type, it's there to avoid the
>UNIONs when I'm trying to get building-level data on multiple building

If you only need a subset of the data, you could also use EXISTS and a subquery with a join on the school or bank table:

SELECT * FROM buildings b

   OR (EXISTS (SELECT * FROM banks bn WHERE = -- etc.

Of course, you should substitute the actual column names you select for the first star. It shouldn't perform too badly IMHO, considering that the joins are on the primary key columns. However, the union might be faster -- you have to test and compare.

But if you need to do any grouping or sorting on the type, you'll probably still have to use a union. I'll admit that it is tempting to have that additional type column in this case, but I consider it to be a little dangerous for the reasons I mentioned in my earlier post.

Bob Hairgrove
Received on Sat Feb 25 2006 - 12:57:36 CET

Original text of this message