Data Mode design Question.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anil G - 19 Feb 2005 01:41 GMT

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

Reply to this Message

Jerry Gitomer - 19 Feb 2005 20:19 GMT

> Hello,
>
[quoted text clipped - 46 lines]
>
> Anil G

Get rid of the recursive definition of shipment package. In the
real world you have:

A shipment consisting of one or more packages.
A package containing one or more of each of one or more parts

Reply to this Message

Mike Sherrill - 20 Feb 2005 14:47 GMT

>Get rid of the recursive definition of shipment package. In the
>real world you have:
>
>A shipment consisting of one or more packages.
>A package containing one or more of each of one or more parts

Actually, in the real world, packages can contain either packages or
parts in any combination.

Signature

Mike Sherrill
Information Management Systems

Reply to this Message

Jerry Gitomer - 20 Feb 2005 16:13 GMT

>>Get rid of the recursive definition of shipment package. In the
>>real world you have:
[quoted text clipped - 4 lines]
> Actually, in the real world, packages can contain either packages or
> parts in any combination.

In order to get things as simple as possible ignore the inner
package. Just treat it as though it is transparent and treat
its contents as parts contained in the outer package.

If this is not possible one simplistic solution is to put
packages and parts in the same table, add a column that will
allow you determine which you are dealing with, add a
parent-child table (a many-to-many table) and then write some
application code that will properly process packages that
contain packages.

As you can see it is much easier to implement and maintain the
application if you can treat the inner package as being transparent.

HTH
Jerry

Reply to this Message

Mike Sherrill - 21 Feb 2005 14:07 GMT

>> Actually, in the real world, packages can contain either packages or
>> parts in any combination.
>>
>In order to get things as simple as possible ignore the inner
>package.

Is that another way of saying, "To get things as simple as possible,
ignore the way the world works"? ;)

Signature

Mike Sherrill
Information Management Systems

Reply to this Message

Jerry Gitomer - 21 Feb 2005 16:11 GMT

>>>Actually, in the real world, packages can contain either packages or
>>>parts in any combination.
[quoted text clipped - 4 lines]
> Is that another way of saying, "To get things as simple as possible,
> ignore the way the world works"? ;)

On reflection it seems to me that to do other than ignore the
inner package is to ignore the way the world works. Picture the
scene at the shipping dock. The UPS/FedEx/DHL person shows up
to pick up todays shipment to customer X. The shipping papers
will specify the number of packages in the shipment. The
"inner" packages will be ignored since they can't be seen or
counted or tracked.

Reply to this Message

Mike Sherrill - 21 Feb 2005 17:51 GMT

>On reflection it seems to me that to do other than ignore the
>inner package is to ignore the way the world works.

I'll admit that there's abundant precedent for that, but I don't think
it's a good idea.

>Picture the
>scene at the shipping dock. The UPS/FedEx/DHL person shows up
>to pick up todays shipment to customer X. The shipping papers
>will specify the number of packages in the shipment. The
>"inner" packages will be ignored since they can't be seen or
>counted or tracked.

Picture the scene at the distribution center, where someone opens the
shipping crate and starts putting the other parcels into warehouse
inventory. And "picture" the use of RFID.

Signature

Mike Sherrill
Information Management Systems

Reply to this Message

Neo - 19 Feb 2005 21:30 GMT

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 SP2
// 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)))

Reply to this Message

Ed Prochak - 22 Feb 2005 21:04 GMT

> Hello,
>
[quoted text clipped - 3 lines]
>
> And Data requirement is :

[]

> Shipment Package needs to have following:
>
[quoted text clipped - 11 lines]
> SP2
> XBV QTY: 15

[]

> 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.

There's a good reason why it doesn't work. Note SP4 contains SP1 and
SP2, and SP2 contain SP1, so there's an inconsistancy here. Do you mean
that SP4 contains SP2 contains SP1 (ie there's 1 SP1 within SP 4) or
that SP4 contains both SP2 and SP1 (and since SP2 contains SP1, there
is a total of 2 SP1s within the SP4 package)? IOW, with SP4 are you
shipping a total of 25 XYZ parts, or 40 XYZ parts?

Ed

Reply to this Message

Anil G - 27 Feb 2005 07:01 GMT

Following is the data model design i am comming up with; I hope i am
diagramming correct with text pad.... btw i use Designer for data model
design.

Shipment Item 1-----------* Packing Content *----------------Shipment
Package
*| * | |*
|
| |

|--------------Level - 1----------------|

| |
|
|

|----------------Level - 2----------------|

|
|

|------------------Level - 3----------------|

Above model had one flaw that i have to harcorde releationship for
nested level packing upto three levels, i can not have N-Level Packing
structure :(

Reply to this Message