Re: Design question regarding data typing

"Jacob JKW" <jacobcdf_at_yahoo.com> wrote in message news:1140751712.900035.59400_at_v46g2000cwv.googlegroups.com...
> (I'm new to database design so I do hope I'm able to adequately
> describe my issue.)
>
> I currently have a table called Institutions. The table groups data
> common to all institutions along with a foreign key to the
> InstitutionType table. Institution types can be let's say either
> "school" or "bank".
>
> The similar characteristics of both schools and banks are all grouped
> in the Institutions table, and the dissimilar characteristics are all
> grouped in the Schools and Banks table respectively.
>
> I assume this is rather poor DB design.

Why do you assume that? It is a perfectly reasonable design.

> I'd ideally like to somehow
> get rid of the school/bank identifier in the Institutions table but
> it's important to me to be able to identify the type of each
> Institution when summarizing common Institution characteristics.

The presence of a discriminator is not logically required. Unfortunately if you are planning to build this database using SQL you are stuck with it if you want to have any hope of enforcing useful integrity constraints.

> Obviously, one convoluted way to get around this would be to pull all
> Instutions FKs from the Schools and Banks and then based upon the table
> in which I found the FK I could determine the type. But that seems kind
> of silly. Shouldn't I be able to do this without having to do any
> lookups in the two child tables?
>
> so any suggestions on where I'm going wrong and/or how better to
> proceed?

I don't understand what your problem is. Is it performance? Aesthetics? Consistency? Fidelity to the enterprise of interest?

Roy Received on Fri Feb 24 2006 - 10:05:43 CET