1

I am working in Access 2010.

Let's say I have a database that tracks where packages sit in a warehouse.

Table: Packages
PackageID (PK)
PackageHeight
PackageWidth
PackageWeight
PackageOwner
(etc)

Now I want to show what packages each package is next to.

For example, A is next to B and M. 

Table: PackageNextTo
ID (PK), PackageID (Packages.PackageID, FK), NextToPackageID(Packages.PackageID, FK)
1, A, B
2, A, M

So when I look up A, I find out it is next to B and M.

But when I look up B, I also want to find out it is next to A.... and when I look up M, I want to find out it is next to A and also, for example, next to T. (but M and B are not necessarily next to each other). Do I have to define these relationships explicity by adding records as follows:

Table: PackageNextTo
ID (PK, AutoIncrement), PackageID, NextToPackageID
1, A, B
2, A, M
3, B, A
4, M, A
5, M, T
6, T, M

OR is there a way to automatically say if x is next to y then y is next to x.

(OR a better way to structure this data altogether!!)

In human terms I see redundancy between records 1 and 3 above, and between 5 and 6 above, and so on.... but I don't know if this is redundant to a database or if the database needs this explicitly called out.

If the database requires this explicit, is there a way to run a macro or query that will automatically create (B,A) when (A,B) is defined?

maneesha
  • 197
  • 1
  • 2
  • 8

3 Answers3

2

In your proposed PackageNextTo table, "ID (PK)" appears to be a surrogate key, presumably an autonumber. While I'm not a surrogate key advocate myself, I know folk who are who avoid them in a relationship tables (junction tables, linking tables, whatever). You would certainly need to enforce the candidate key (compound) of (PackageID, NextToPackageID) anyhow and the presence of the ID seems to have confused at least one person answering here. Therefore, I suggest you omit it.

To ensure the 'next to' relationship are stored uni-directional, add a CHECK constraint (or Validation Rule) e.g. 

CREATE TABLE PackageNextTo
(
 PackageID CHAR(1) NOT NULL 
    REFERENCES Packages (PackageID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 NextToPackageID CHAR(1) NOT NULL 
    REFERENCES Packages (PackageID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 CHECK (PackageID < NextToPackageID), 
 UNIQUE (PackageID, NextToPackageID)
);

To show all 'next to' relationships as bi-directional, expand them using a VIEW (or create a soted Query object in your usual way):

CREATE VIEW AdjacentPackages
AS
SELECT PackageID AS PackageID_1, 
       NextToPackageID AS PackageID_2
  FROM PackageNextTo
UNION
SELECT NextToPackageID AS PackageID_1, 
       PackageID AS PackageID_2
  FROM PackageNextTo;

Note that CHECK and CREATE VIEW require ANSI-92 Query Mode.

onedaywhen
  • 2,632
  • 1
  • 20
  • 21
1

Create a view as follows:

SELECT ID, PackageID, NextToPackageID FROM PackageNextTo
UNION ALL
SELECT ID, NextToPackageID, PackageID FROM PackageNextTo

(this is Oracle syntax and may need to be modified for Access)

Leigh Riffel
  • 23,884
  • 17
  • 80
  • 155
0

Do you have any locations that the packages are stored in/at (like shelf number/silo/etc)? If you do, then you can have a table with locations and package at that location. To find the adjacent packages you need only to know the location id +/-1 location. The location ID would need to be unique and sequential.

If not, then you would could define a constraint (which probably means using a database engine that is more powerful the Access, maybe SQL server express?) Then you could create a trigger function that checks that the X next to Y == Y next to X on any updates.

Another option is to create a linked list structure in your table, so that the data element for X has next filled and Y and the previous element for Y has X, with the prev/next/current fields having a unique constraint on them. Eg 

Table: Packages
PackageID (PK)
PrevPackageID (unique, allow null)
NextPackageID (unique, allow null)
PackageHeight
….

And then to be real sure put in a constraint or trigger function to check that prevID matches the nextID of the previous element.

adam f
  • 674
  • 3
  • 3