Re: Design question regarding data typing

On 24 Feb 2006 14:50:48 -0800, "Jacob JKW" <jacobcdf_at_yahoo.com> 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
>types.

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
WHERE (EXISTS (SELECT * FROM schools s WHERE s.id = b.id))

   OR (EXISTS (SELECT * FROM banks bn WHERE bn.id = b.id)) -- 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
NoSpamPlease_at_Home.com