Re: Design question regarding data typing

Bob Hairgrove wrote:
> 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.
OK. But I don't actually see how I could accomplish this with a "simple natural join". To me it seems I'd have to use a union. Let's say I've got this:

CREATE TABLE buildings (

    building_id VARCHAR(100) NOT NULL,
    building_color VARCHAR(100) NOT NULL,
    building_type VARCHAR(100) NOT NULL,

• other columns... CONSTRAINT pk_buildings PRIMARY KEY (building_id));

CREATE TABLE schools (

    building_id VARCHAR(100) NOT NULL,
    school_name VARCHAR(100) NOT NULL,

• other columns... CONSTRAINT pk_schools PRIMARY KEY (building_id), CONSTRAINT uk_schools UNIQUE (school_name), CONSTRAINT fk_schools FOREIGN KEY (building_id) REFERENCES buildings (building_id));

CREATE TABLE banks (

    building_id VARCHAR(100) NOT NULL,
    BIC VARCHAR(10) NOT NULL,

• other columns... CONSTRAINT pk_banks PRIMARY KEY (building_id), CONSTRAINT uk_banks UNIQUE (BIC), CONSTRAINT fk_banks FOREIGN KEY (building_id) REFERENCES buildings (building_id));

Now let's say all I'm looking for is a table of building_id's, building_color's, and building_type's. That's just:

    SELECT building_id, building_color, building_type FROM buildings;

But let's say that I get rid of the troublesome building_type column:

    ALTER TABLE buildings DROP COLUMN building_type;

Then the simplest way I can think to accomplish the same thing would be with the union:

    (

        SELECT buildings.building_id, building_color, "school" AS building_type

          FROM buildings INNER JOIN schools
          ON buildings.building_id=schools.building_id
    ) UNION (
        SELECT buildings.building_id, building_color, "bank" AS
building_type
          FROM buildings INNER JOIN banks
          ON buildings.building_id=banks.building_id

I guess what I'd *like* to have would be a building_types table which along with a short text description of the specific building type contained a pointer to the appropriate subtype table (banks, or schools, or hospitals, or whatever). But I have no idea of this is even possible in standard SQL. Received on Fri Feb 24 2006 - 20:55:47 CET