Ms-access – How to create a database with “polymorphism”

foreign keyms access

I'm sorry I'm new when it comes to databases, I'm using Access right now to develop a database to store calibration data for a bunch of different types of transducers. Each type of item is fundamentally different from the other so I'm planning to make a table for each item type, but across the board they all must have a unique ID number (so it can be related to one or many calibrations). Here is an example in rusty SQL, hopefully it makes sense.

create table accelerometer (GAC_id, name, range, serial)
create table loadcell (GAC_id, name, description)
create table potentiometer (GAC_id, product_id, tag_id)

As you can tell the 3 tables have different columns but across the 3 tables there MUST be a UNIQUE GAC_id number, something that no matter what table there is only one GAC_id in the entire database.

One idea is if I were to create a 'master table' that had a GAC_id and a part type then would have a relationship to the appropriate sub-category part id but I don't think you can create a 'variable' relationship, can you? Following the example above…

create table Part (GAC_ID, part_type, accelerometer_ID/loadcell_ID/potentiometer_ID)

… where it chooses the third column based on the value in the second column

Anyway really need help here, thanks

Best Answer

Your best bet is likely going to be to create a single child table that references all three of the other tables onto a single GAC_id. So you'd end up with something more like this:

create table gac (
    GAC_id,
    accelerometer_id,
    loadcell_id,
    potentiometer_id,
    primary key (GAC_id),
    foreign key (accelerometer_id) refrences (accelerometer.accelerometer_id)
    foreign key (loadcell_id) references (loadcell.loadcell_id)
    foreign key (potentiometer_id) references (potentiometer.potentiometer_id)
)

Here, each foreign key should reference the primary from the appropriate table:

create table accelerometer (
    accelerometer_id,
    name,
    range,
    serial,
    primary key (accelerometer_id))
create table loadcell (
    loadcell_id,
    name,
    description,
    primary key (loadcell_id))
create table potentiometer (
    potentiometer_id,
    product_id,
    tag_id,
    primary key (potentiometer_id))

This will work from a relational standpoint if I understand what you are looking for correctly. Then you just make sure that all items in gac have a unique index associated with them, they cannot be null, and that each entry has to have a corresponding entry in the appropriate parent table. For Access specific SQL, use the MSDN Access SQL Reference to see Access specific queries to perform each task.

From a design standpoint, it would be a horrible idea to have three different tables with identical primary keys (GAC_ids in this case). Theoretically you could do it, but there are all sorts of issues that it can and will cause from a practical standpoint.

Theoretically you could also do something like what you had originally:

create table accelerometer (GAC_id, name, range, serial, primary key(GAC_id, <insert one of the other fields here>))
create table loadcell (GAC_id, name, description, primary key(GAC_id, <insert one of the other fields here>))
create table potentiometer (GAC_id, product_id, tag_id, primary key(GAC_id, <insert one of the other fields here>))

Then you could just create a view that joins them all on the GAC_id. Of course, this will only work if the GAC_ids correlate one-to-one:

create view my_view as select * from accelerometer a
    full outter join loadcell lc ON a.GAC_id = lc.GAC_id
    full outter join potentiometer p ON a.GAC_id = p.GAC_id

That would create a view that accomplishes the same task as the child table in the first explanation i gave, but it has the advantage of auto updating whenever an item is added to any table. the only catch is that if there is no corresponding entry in any of the other tables, you will see null values where those entries should be.

look into the MSDN Access SQL Reference for the specifics on how to do the different things i've mentioned, in Access SQL.

One thing that is worth mentioning is that Access SQL may not support full outter joins. I know that is a quirk with MySQL, but i am not familiar with Access SQL, so you will have to use the reference, and probably this website(dba.stackexchange.com) and google to see exactly how you can/should build your create queries in Access SQL.

Firstly, a schema...

tblLocation(locationID[PK++], locationReference)
tblRoom(roomID[PK++], keyLocation[tblLocation.locationID], roomNumber)
tblRack(rackID[PK++], keyRoom[tblRoom.roomID], rackReference)
tblDevice(deviceID[pk++], keyRack[tblRack.rackID], positionU, deviceLabel)

...tables for custom devices can extend tblDevice

Foreign keys in square brackets, PK++ means an auto increment primary key.

As you can see, a Location has many Rooms, a Room has many Racks, a Rack has many Devices.

I have made primary keys as auto IDs as I prefer them to be integers and there's less to think about should you change a location/room/rack reference.

Populate this with Locations, Rooms and Racks as appropriate.

Solution 1 :: Not a lot of racks

Have three drop downs on the form.
User chooses a location, you then select all the rooms of that location in the next combobox.
User then selects the room, you then fill the next combobox with the racks in the room.
User then fills in device details and U position (which you probably want to validate to make sure that you don't have anything allocated there.)

Solution 2 :: Lots of racks

Have a query similar to:

SELECT
    rackID,
    locationReference + '-' + roomReference + '-' + rackReference AS [Full Location]
FROM tblLocation
    INNER JOIN tblRoom
        ON tblLocation.locationID = tblRoom.keyLocation
    INNER JOIN tblRack
        ON tblRoom.roomID = tblRack.keyRoom
ORDER BY
    locationReference ASC,
    roomNumber ASC,
    rackReference ASC

To generate a list of Location-Room-Rack references.

ABC-101-A/1
ABC-101-A/1
ABC-101-A/2
ABC-101-A/2
ABC-105-B8
ABC-105-B8
FAY-208-X8
FAY-210-A9T
FAY-210-A9T
FAY-210-F4
NAT-410-78
NAT-410-78

User selects one, then fills in the information. List will be equal to the number of racks (which could get a bit excessive).

Advantages

If you move a rack (or racks) of equipment from one room to another, it's just a simple update query to update the database
If a location/room/rack reference changes, you only have one piece of data to update.

Further schema additions

I've used racks in the past, I simplified the schema but these are some additions that spring to mind.

Add rackSize to tblRack: the size in U's
Add sizeU to tblDevice: the size of the device in U's

You can then do further validation: if you have a 39U rack and somebody tries to put a 5U device in at location 38, you can throw an error (as U's 40-42 don't exist!). Also if somebody puts a 3U device in at location 10, and something is in location 11, you know it won't fit or they have not updated the details of the device that is no longer there!

You could also make some clever queries that would tell you how much space there is in your racks (and where). But that's beyond the scope of this question!

Hope this helps.

How to relate two rows in the same table

What you have designed is good. What needs to be added is a constraint to make the relationship directionless. So, you cannot have a (1,5) row without a (5,1) row being added as well.

This can be accomplished^* with a self referencing constraint on the bridge table.

*: _{it can be accomplished in Postgres, Oracle, DB2 and all DBMS that have implemented foreign key constraints as the SQL standard describes (deferred, e.g. checked at the end of transaction.) Deferred checking is not really needed anyway, as in SQL-Server that checks them at the end of statement and this construction still works. You cannot do this in MySQL because "InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row".}

So, in Postgres the following will match your requirements:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

Tested at: SQL-Fiddle

If you try to add a row (1,5):

INSERT INTO bridge_x VALUES
(1,5) ;

It fails with:

ERROR: insert or update on table "bridge_x" violates foreign key constraint "x_x_directionless"
Detail: Key (x_id2, x_id1)=(5, 1) is not present in table "bridge_x".:
INSERT INTO bridge_x VALUES (1,5)

Additionally, you can add a CHECK constraint if you want to forbid (y,y) rows:

ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

There are other ways to implement this as you mention, like storing only one direction of the relationship (in one row, not two) by forcing the lower id in x_id1 and the higher id in the x_id2 column. It looks easier to implement, but usually leads to more complex queries later:

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);