"Pointing" the same column in an Access table to different types of tables...

Question

This is a mechanical factory type application.

I know I can do this brutally via programming, but is there a "clean" RDB way of doing it...?

I have a table with 4 key columns. The 4 key columns are a lookup into a 5th, the 5th column is the "answer".

The first two columns are SAP codes (8 digit numerical codes) for physical objects. The 5th column is the time required to fix the two physical objects together. Columns 3 and 4 are not relevant to my question.

Now my problem is that some SAP codes correspond to tubes of varying diameters, thicknesses and materials and they exist in the Tubes table.

Other SAP codes are distinct objects (valves etc), in an Objects table.

It is mechanically possible to fix:

1. a tube to a tube
2. an object to an object
3. a tube to an object.

Before the introduction of objects it was easy, each SAP code in the first two columns corresponded to a single row in the Tubes table.

But with the introduction of "Objects" I'm not sure how to keep the RDB database design "clean".

I'm no DB expert, so thanks in advance for any help or pointers


Owen

---

http://www.ransen.com Cad and Graphics software

Answer 1

From your description I think you have a type of which tubes and objects are sub-types.  A sub-type is characterized by sharing all attributes of its (super) type, but no those of other sub-types of the same (super) type.  It is modelled by a one-to-one relationship type between the (super) type table and each sub-type table, i.e. the primary key of each sub-type table is also a foreign key referencing the primary key of the  (super) type table.  So SAPCode would be the primary key of the three tables in your case.

You can then model the three possible relationships which you describe by a third table, with two foreign columns referencing the primary key of the (super) type table, and hence indirectly the primary key of the relevant sub-type table.

For an example of a simple type hierarchy, using the personnel of an academic institution as its example,  you might like to take a look at TypeHierarchy.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

---

Ken Sheridan, Stafford, England

Answer 2

Thanks for your reply.

"which tubes and objects are sub-types"

The "objects" are generally valves (at the moment).

So you are saying make a super type and put tubes and valves as sub types under it?

(I've never heard of this in Access, but you learn something everyday!)

---

http://www.ransen.com Cad and Graphics software

Answer 3

I've had a look at TypeHierarchy.zip and it all became clear to me when I looked at the relations diagram.

I suddenly understood they were like derived classes in C++

Many thanks!

---

http://www.ransen.com Cad and Graphics software

Answer 4

Thanks Ken.

Seems there are very few examples of Sub/Super type (Generalization/Specialization) from logical to physical. Happy to find this. Thanks.