database supertype/subtype

Question

I need to implement a database to track a parcel's location.

There can be basically 4 locations, namely on a aeroplane, on a ship, on a car, at a transit office.

I need to have separate entities for these 4 locations because they all have mutiple instances of each (e.g. many aeroplanes, many cars)

Also, each of these 4 entities are managed by one and only one employee (another entity).

I have created an associative entity called job whereby it stores the information about which employee working in each of the 4 locations, similar to a flight number which contains the aeroplane id with the pilot id.

I have examined the attributes of the 4 locations, and found that they could be classified into car being the supertype and aeroplane, ship and office being subtype of car.

there would be a unique attribute of location_id which tells me whether it is on an aeroplane or an office in the supertype, thus the subtype would inherit this attribute.

my question is whether in the tables design, since locations will be separated into four different tables, whenever i referred location_id(FK) from the job entity, is it still possible to do so since a foreign key normally links to only one table and not 4 tables in this case..

EMPLOYEE [ employee_id, employee_name, gender, contact_number, job_scope ]

JOB [ job_id, (employee_id), (location_id) ]

OFFICE [ location_id, office_id, address ]

CAR [ location_id, vehicle_id ]

AEROPLANE [ location_id, vehicle_id, type, unladen_weight ]

SHIP [ location_id, vehicle_id, size ]

We seem to have a little misunderstanding here, my bad. See my updated answer below. — p.marino, Oct 11 '11 at 09:35

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

Create a Location table.

Location-id
Location-Type
Description

(N.B.: the second field is redundant, because if the Location is a "Ship" you will find a record in the Ship table and nowhere else, but may be nice to have)

Location-ID will be the primary key of the Location Table. And it will be a foreign key for Ship, Air, Office etc.

So let's say that you want to map 2 "locations", one is a ship, the other is an office.

Location table:

Location-Id | Location-Type | Description
      ...   | ...           | ... 
     005768 | Ship          | "The Sea Witch" Mexican Cargo
      ...   | ...           | ...
     087956 | Office        | "Our offices in Albuquerque"

Office Table

Office-Id | Address 
   ...    | ...
   087956 | "145 Rose St. Albuquerque"
   ...    | ...

Ship Table

  Ship-Id | Size 
   ...    | ...
   005768 | 14000
   ...    | ...

In case you manage to find some other field which exists for all 4 types of "locations" add it to the Location table.

The rest should be more or less ok: your Job table will point here, and every "concrete" class will be obtained by joining the Location record with the specific "subtype" record.

Note: If you meant "location" as something else, for example "geographical area" like "Arizona" or "Gulf of Mexico" you will have to add this as an attribute of the Location table. For Office it will be static, while the other types of "locations" may change it as they move around.

So the supertype is "Location", and Office or Ship are subtypes of the "generic" location.

This is a standard way to model Supertypes in RDBMS, btw, as explained already in some other places.

edited May 23 '17 at 11:55

Community

1
1

answered Oct 11 '11 at 07:36

p.marino

6,244
3
25
36

so in this case the supertype would be the location table, but how would the location table be linked to the 4 different locations, taking into account there are many instances of aeroplanes, ships etc. LOCATION [location_id, description] ? ship [ vehicle_id, unladen_weight ] ? – BOOnZ Oct 11 '11 at 09:05
and I am only currently in the stage of designing so I actually do not quite understand the way "in some other places" is explaining in coding.. (I learning using drawing models in rectangles and crow foot notations as of now) – BOOnZ Oct 11 '11 at 09:06
The "some other places" is one of the many explanations about mapping objects hierarchies in RDBMS, and it cites this: http://www.agiledata.org/essays/mappingObjects.html#MappingInheritance which is not "coding", and fairly complete. – p.marino Oct 11 '11 at 09:33
I roughly got the idea already, so location_id would be foreign keys in office, aeroplane, ship and car. because so far to my understanding, its a direct relation between the foreign key which links it to the pk of another table. so if we were to model it as this supertype example, the pk would actually be linked to four fk, which in actual implementation is actually possible? – BOOnZ Oct 11 '11 at 10:02
If you "turn the problem around" and make it a FK from Ship To Location, for example, you won't have any problem. Because for each of the 4 subtables the FK points to one table only (Location). And the Job-to-Location is direct, too... – p.marino Oct 11 '11 at 10:26
How does one decide when to have the subtables point to the locations table, or the locations table point to the subtables? – Jimmy Apr 25 '12 at 14:50
1

@Jimmy - subtypes "point" to the superclass. This is really obvious to decide because (at least in conventions RDBMS) you can't have a FK going to more than one table, as the OP says in his comment above. – p.marino Apr 25 '12 at 21:29
What does the query look like to display (say) an employee's specific location? How does the query know to which subtype to map the Location-Id? – Jimmy Apr 26 '12 at 13:37
This is why I suggested, in the original answer, to add a "location-type" field as a convenience. You can leverage that or use views (one for location type). – p.marino Apr 27 '12 at 21:47

database supertype/subtype

1 Answers1