Re: Design question regarding data typing

From: Bob Hairgrove <invalid_at_bigfoot.com>
Date: Fri, 24 Feb 2006 11:53:20 +0100
Message-ID: <rentv1lt87qhpb6sci4ghktd3agub8if2s_at_4ax.com>


On 23 Feb 2006 19:28:33 -0800, "Jacob JKW" <jacobcdf_at_yahoo.com> wrote:

>(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. 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.
>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?

It does sound like an awkward design to me. For example, both schools and banks have a physical address, or location, as well as one or more mailing addresses and a variety of phone and fax numbers, e-mail, etc. So do ordinary people. And there might some day be an additional new entity with the same attributes. Therefore, I would keep the address and phone data, etc. in separate tables.

If you are more interested in modelling a map with longitude and latitude, perhaps modelling the physical property (i.e. real estate), then you would typically try to abstract the schools and banks as specializations of a base type, perhaps "building". The usual way of modelling this would be to have a table of buildings, schools and banks. The schools and banks tables would have a 1-to-1 foreign constraint on the primary keys, e.g.:

CREATE TABLE buildings (

    building_id VARCHAR(100) NOT NULL, -- 100 is just arbitrary     longitude FLOAT NOT NULL, -- reference point for location     latitude FLOAT 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,
    school_type VARCHAR(100) NOT NULL, -- e.g. "high school"
  • etc. 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,
    bank_name VARCHAR(100) NOT NULL,
    BIC VARCHAR(10) NOT NULL, -- int'l. bank code

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

You would then need a separate table to join buildings to physical addresses. However, for the mailing addresses ad phone numbers, I would link the apropriate specialization (i.e. school or bank) and not the building. That means that you would have one table "SchoolAddresses" and another "BankAddresses", etc. Otherwise, your queries would need to have joins on three tables instead of just two.

--
Bob Hairgrove
NoSpamPlease_at_Home.com
Received on Fri Feb 24 2006 - 11:53:20 CET

Original text of this message