Shipment Packaging Data Model Question

Hello,

I have following design for Shipment and Packaging

Shipment Item ---------* Packing Content *----------- Shipment Package

And Data requirement is :

Shipment Item
--------------
ID PART # PART CONTROL QTY
-- ----- ------------ ----
21. ABC NA 50
22. XYZ PK#1 10
23. XYZ PK#2 15
24. XYZ PK#3 20
25. ABX NA 30
26. XBV NA 20

Now,
Shipment Package needs to have following:

1. SP1 consists of:
PART# ABC QTY: 10
XYZ QTY: 15
2. SP2 consists of:
SP: SP1
XYZ QTY:10
3. SP3 consists of
XYZ QTY: 20
ABX QTY: 30
4. SP4 consists of
SP1
SP2
XBV QTY: 15
5. SP5:
XBV QTY: 5
7. SP6:
SP4
SP5

etc... cause there are various nested levels of shipment packages can
be achieved,

Question is anyone has any sugguestions on these model, casue
Parent-Child case will not work in this case.

Thanks in advance for resposes,

Anil G

Shipment Item
ID PART # PART CONTROL QTY
-- ----- ------------ ----
21. ABC NA 50
22. XYZ PK#1 10
23. XYZ PK#2 15
24. XYZ PK#3 20
25. ABX NA 30
26. XBV NA 20

1. SP1 consists of:
PART# ABC QTY: 10
XYZ QTY: 15
2. SP2 consists of:
SP: SP1
XYZ QTY:10
3. SP3 consists of
XYZ QTY: 20
ABX QTY: 30
4. SP4 consists of
SP1
SP2
XBV QTY: 15
5. SP5:
XBV QTY: 5
7. SP6:
SP4
SP5

Below is a solution using an experimental db (XDb2):

// Create items in directory to classify things.
(CREATE *shipPkg.item ~in = dir)
(CREATE *shipItem.item ~in = dir)
(CREATE *part#.item ~in = dir)
(CREATE *control.item ~in = dir)
(CREATE *qty.item ~in = dir)

// Create shipment items.
(CREATE *.cls = shipItem
& it.part# = +ABC
& it.qty = +50)
(CREATE *.cls = shipItem
& it.part# = +XYZ
& it.control = +PK#1
& it.qty = +10)
(CREATE *.cls = shipItem
& it.part# = +XYZ
& it.control = +PK#2
& it.qty = +15)
(CREATE *.cls = shipItem
& it.part# = +XYZ
& it.control = +PK#3
& it.qty = +20)
(CREATE *.cls = shipItem
& it.part# = +ABX
& it.qty = +30)
(CREATE *.cls = shipItem
& it.part# = +XBV
& it.qty = +20)

// Create verb "contain"
(CREATE *contain.cls = verb)

// Create shipment package 1.
// Note: I assumed you meant 50 not 10 of ABC
// as there is no such shipment item.
(CREATE *SP1.cls = shipPkg
& it.contain = (*.cls = shipItem
& *.part# = ABC
& *.qty = 50)
& it.contain = (*.cls = shipItem
& *.part# = XYZ
& *.qty = 15))

// Create shipment package 2.
(CREATE *SP2.cls = shipPkg
& it.contain = SP1
& it.contain = (*.cls = shipItem
& *.part# = XYZ
& *.qty = 10))

// Create shipment package 3.
(CREATE *SP3.cls = shipPkg
& it.contain = (*.cls = shipItem
& *.part# = XYZ
& *.qty = 20)
& it.contain = (*.cls = shipItem
& *.part# = ABX
& *.qty = 30))

// Create shipment package 4.
// Note: I assumed you meant 20 not 15 of XBV
// as there is no such shipment item.
// Also note: SP1 was already included in SP
// so it shouldn't be part of SP4, unless SPs are templates.
(CREATE *SP4.cls = shipPkg
& it.contain = SP1
& it.contain = SP2
& it.contain = (*.cls = shipItem
& *.part# = XBV
& *.qty = 20))

// Create shipment package 5.
// Note: I assumed you meant 20 not 5 of XBV
// as there is no such shipment item.
(CREATE *SP5.cls = shipPkg
& it.contain = (*.cls = shipItem
& *.part# = XBV
& *.qty = 20))

// Create shipment package 6.
(CREATE *SP6.cls = shipPkg
& it.contain = SP4
& it.contain = SP5)

// Find shipment package which contains
// a shipment item whose part# is XBV
// and contains another shipment package
// which contains shipment item with part# XYZ
// Finds shipment package 4.
(SELECT *.cls = shipPkg
& *.contain = (*.cls = shipItem
& *.part# = XBV)
& *.contain = (*.cls = shipPkg
& *.contain = (*.part# = XYZ)))