G
Guest
I would like to know if MS Access can handle the following problem and if I
could get a few pointers on how to tackle this situation.
Problem Description:
- I have a table which describes a series of Items (e.g. ItemID (PK),
ItemName)
- I have another table with describes a series of packages (e.g. PackageID
(PK), PackageType).
- Fundamentally a package contains items (and items may be repeated within a
package).
- However, a package can also contain multiple other packages (sub-packages)
along-side other (unpackaged) items.
- There is no limit to how many (sub-)packages can be contained within other
(parent) packages.
- A package must at least contain another package or an item.
- I want to setup a linking table that lists what packages are composed of
what "components", where a component can either be an item or another
package. (e.g. ComponentPlacementID (PK), ComponentID)
e.g.: each entity in the following linking table would consist a "placement"
which describes (which package) (contains "=") (which "component")
* P1= i1
* P1 = i2
* P1 = i2
* P1 = i3
* P2 = i1
* P2 = P1
* P3 = i5
* P3 = P2
* P4 = i5
* P4 = P3
- Each "placement" (assignment of package-to-item or package-to-subpackage )
is not necessarily unique. (e.g. two Item i2 are contained in package p1,
furthermore, a package could contain two sub-packages p2). e.g. a "package"
could be a type of food box, and an "Item" could be at type of fruit, e.g. an
apple.
- The primary key (PK) for each table is set to AutoNumber.
Here is where I am stuck: How to make the ComponentID in the linking table
related to two different types of Primary keys (ItemID or PackageID). The
problem I see is that since Item i1 may have ItemID=1 and Package P1 may have
ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what table
to link to.
What is the best way to handle such a scenario? Do I need to include
another field in the "placement" linking table which tells whether this is a
package or item assignment (Yes/No field). Or, do I need to include separate
fields, one for Item assignments which would be related to ItemID and a
separate field for package assignments which is related to PackageID, and
somehow, be careful to make sure that each entity has either: (an ItemID
specified XOR a PackageID specfied)? Or, should I somehow make the ItemID
and PackageID mutually unique and let this unique ID carry the relationship
information?
The end goal is to be able to querry a given package (say P4), and list its
components (i5, P3) and furthermore list all the fundamental items and
quantity thereof contained in entire package P4 (as if all sub-packages would
be opened and all items would be thrown together into the same box, e.g.
2xi1, 2xi2, 1xi3, 2xi5).
Any suggestion would be greatly appreciated.
could get a few pointers on how to tackle this situation.
Problem Description:
- I have a table which describes a series of Items (e.g. ItemID (PK),
ItemName)
- I have another table with describes a series of packages (e.g. PackageID
(PK), PackageType).
- Fundamentally a package contains items (and items may be repeated within a
package).
- However, a package can also contain multiple other packages (sub-packages)
along-side other (unpackaged) items.
- There is no limit to how many (sub-)packages can be contained within other
(parent) packages.
- A package must at least contain another package or an item.
- I want to setup a linking table that lists what packages are composed of
what "components", where a component can either be an item or another
package. (e.g. ComponentPlacementID (PK), ComponentID)
e.g.: each entity in the following linking table would consist a "placement"
which describes (which package) (contains "=") (which "component")
* P1= i1
* P1 = i2
* P1 = i2
* P1 = i3
* P2 = i1
* P2 = P1
* P3 = i5
* P3 = P2
* P4 = i5
* P4 = P3
- Each "placement" (assignment of package-to-item or package-to-subpackage )
is not necessarily unique. (e.g. two Item i2 are contained in package p1,
furthermore, a package could contain two sub-packages p2). e.g. a "package"
could be a type of food box, and an "Item" could be at type of fruit, e.g. an
apple.
- The primary key (PK) for each table is set to AutoNumber.
Here is where I am stuck: How to make the ComponentID in the linking table
related to two different types of Primary keys (ItemID or PackageID). The
problem I see is that since Item i1 may have ItemID=1 and Package P1 may have
ItemID=1 then P2 contains two ComponentID=1 but nothing tells it what table
to link to.
What is the best way to handle such a scenario? Do I need to include
another field in the "placement" linking table which tells whether this is a
package or item assignment (Yes/No field). Or, do I need to include separate
fields, one for Item assignments which would be related to ItemID and a
separate field for package assignments which is related to PackageID, and
somehow, be careful to make sure that each entity has either: (an ItemID
specified XOR a PackageID specfied)? Or, should I somehow make the ItemID
and PackageID mutually unique and let this unique ID carry the relationship
information?
The end goal is to be able to querry a given package (say P4), and list its
components (i5, P3) and furthermore list all the fundamental items and
quantity thereof contained in entire package P4 (as if all sub-packages would
be opened and all items would be thrown together into the same box, e.g.
2xi1, 2xi2, 1xi3, 2xi5).
Any suggestion would be greatly appreciated.