Re: Design question regarding data typing

On 24 Feb 2006 09:39:43 -0800, "Jacob JKW" <jacobcdf_at_yahoo.com> wrote:

>Sure. I was trying to explain that one column of my Insitutions table
>was the foreign key InstitutionTypeId which indexes the name (and
>description) of the type of institution (which in this simplification
>can be either "school" or "bank"). I have this in addition to separate
>Banks and Schools tables. The reason for using the InstitutionType
>descriptor (or as I gather it's called from Roy's post above,
>"discriminant") is so that if I want summary instituition data from
>Insitutions I can easily figure out the institution type without having
>to scan for each InstutionId foreign key of interest in Banks Schools.
>And that's the kludge that's causing me such consternation. :-( It just
>reeks of bad form.

Yes, InstitutionTypeId is redundant data if you already have separate tables as described. I don't see the need for it unless for some reason you want to avoid the join or subquery which would be necessary without it should you want to group or sort by schools or banks. A simple natural join of two tables on their primary key columns is going to perform very fast in every RDBMS that I know of ... you'll never have to do a full table scan for that.

Maybe you could post some DDL of your existing tables and how you are querying them?

--
Bob Hairgrove
NoSpamPlease_at_Home.com