Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2001
    Posts
    5

    Best way to model?

    I am modeling a container and its items. So far, I've come up with three different ways to model this. I'd like feedback on the "best" way to model this. Also, the following represent containers of items on an order. When these items are delivered, is this the best place to track its state, or do I need to model a separate entity (e.g. DeliveredContainer, DeliveredItem, etc.):

    1) A Container contains many items

    Container(ContainerID PK, ContainerNumber)
    Item(ItemID PK, Quantity)
    ItemContainer(ItemContainerID PK, ContainerQuantity)

    2) Many Containers contain n items per container

    Container(ContainerID PK, ContainerNumber, Quantity)
    Item(ItemID PK, Quantity)
    ItemContainer(ItemContainerID PK, QuantityPerContainer)

    3) Many containers contain many items

    Container(ContainerID PK, ContainerNumber, Quantity)
    Item(ItemID PK, Quantity)
    ItemContainer(ItemID PK FK, ContainerID PK FK)

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Best way to model?

    Originally posted by estebangs
    I am modeling a container and its items. So far, I've come up with three different ways to model this. I'd like feedback on the "best" way to model this. Also, the following represent containers of items on an order. When these items are delivered, is this the best place to track its state, or do I need to model a separate entity (e.g. DeliveredContainer, DeliveredItem, etc.):

    1) A Container contains many items

    Container(ContainerID PK, ContainerNumber)
    Item(ItemID PK, Quantity)
    ItemContainer(ItemContainerID PK, ContainerQuantity)

    2) Many Containers contain n items per container

    Container(ContainerID PK, ContainerNumber, Quantity)
    Item(ItemID PK, Quantity)
    ItemContainer(ItemContainerID PK, QuantityPerContainer)

    3) Many containers contain many items

    Container(ContainerID PK, ContainerNumber, Quantity)
    Item(ItemID PK, Quantity)
    ItemContainer(ItemID PK FK, ContainerID PK FK)

    Thanks!
    I don't understand your solutions (or your problem). More information is needed, e.g. some examples of items, containers etc. Looking at each of your solutions:

    1) Container - OK

    Item - what is Quantity? The total number of instances of this item that exists in stock? The number of instances of this item that can be put into any container? The quantity of material in the item (e.g. 500ml of cola in a bottle)? The number of items in a particular container?

    ItemContainer - what is that? It is apparently unrelated to either Item or Container, despite its name.

    2) Container - what is quantity here?

    Item - what is quantity (see 1)

    ItemContainer - what is that? (see 1)

    3) Container - what is quantity here?

    Item - what is quantity (see 1)

    ItemContainer - this is a many-to-many relationship between Item and Container. Where is the quantity now?

    To come up with a workable solution, one first needs to know what you mean by an Item. Is an Item:
    a) a specific object, e.g. a particular computer with its own serial no?
    b) a type of item, e.g. "500ml Cola Bottle", of which you may have many in stock, but you would not label individually?

    Assuming (b), since you often use the term "quantity" which suggests this meaning, I would have thought you would want something like this:

    Container( ContainerID PK, ... )
    Item( ItemID PK, ItemDescription, ... )
    ContainerItem( ContainerID PK FK, ItemID PK FK, Quantity)

    This allows a container to hold more than one TYPE of item. Example:

    Container
    ID
    123
    234
    456

    Item
    ID, Description
    789, 500ml Cola Bottle
    890, 250g Milk Chocolate Bar

    ContainerItem
    ContainerID,ItemID,Quantity
    123,789,72
    123,890,150
    456,890,300

    ... i.e.
    - Container 123 contains 72 x 500ml Cola Bottles AND 150 x 250g Milk Chocolate Bars
    - Container 456 contains 300 x 250g Milk Chocolate Bars

    I have not delved into tracking order state yet, but I would be expecting to see another entity called Order used to do that, presumably with some relationship between Containers and Orders.

  3. #3
    Join Date
    Nov 2001
    Posts
    5
    Tony,

    Thanks for the very thorough reply. I believe you have answered my questions, even though I did not properly present my problem. Thanks again!

    FYI... The container is supposed to represent various types of containers (e.g. package, barrel, etc.). An item is what is contained in the container. The quantity is supposed to represent how many items are in a container. The model you suggested is how I was thinking. However, because we would like to track both orders of items, items and their corresponding containers, and just containers (e.g. A container with no items) a colleague suggested that I include "quantity" for an item and "quantity" for containers. Please let me know what you think.

  4. #4
    Join Date
    Nov 2001
    Posts
    5
    Tony,

    Thanks for the very thorough reply. I believe you have answered my questions, even though I did not properly present my problem. Thanks again!

    FYI... The container is supposed to represent various types of containers (e.g. package, barrel, etc.). An item is what is contained in the container. The quantity is supposed to represent how many items are in a container. The model you suggested is how I was thinking. However, because we would like to track both orders of items, items and their corresponding containers, and just containers (e.g. A container with no items) a colleague suggested that I include "quantity" for an item and "quantity" for containers. Please let me know what you think.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by estebangs
    Tony,

    Thanks for the very thorough reply. I believe you have answered my questions, even though I did not properly present my problem. Thanks again!

    FYI... The container is supposed to represent various types of containers (e.g. package, barrel, etc.). An item is what is contained in the container. The quantity is supposed to represent how many items are in a container. The model you suggested is how I was thinking. However, because we would like to track both orders of items, items and their corresponding containers, and just containers (e.g. A container with no items) a colleague suggested that I include "quantity" for an item and "quantity" for containers. Please let me know what you think.
    In my design, the ContainerItem.quantity tells you how many of the Item are in the Container. I don't know what Container.quantity or Item.quantity would mean.

    I imagine that an Order would be for Items, and might be fulfiulled by sending the customer one or more Containers of Items - right?

    So we would have:

    Customer( CustID PK, CustName, ... )
    Order( OrderID PK, CustID FK, ... )
    OrderItem( OrderID PK FK, ItemID PK FK, OrderQuantity )

    (e.g. Customer 101 Smith has Order #200 for 100 x Item 789 and 200 x Item 890)

    We already have from before:

    Container( ContainerID PK, ... )
    Item( ItemID PK, ItemDescription, ... )
    ContainerItem( ContainerID PK FK, ItemID PK FK, Quantity)

    (e.g. Container 135 contains 100 x Item 789 and 50 x Item 890; Container 246 contains 150 x Item 890)

    I'm not clear whether your containers are "created" for each order or re-used. Can a Container exist without an Order? Assuming it can:

    OrderContainer( OrderID PK FK, ContainerID PK FK )

    This assigns Containers to fulfill an order (hmm, maybe not quite right - can a Container be assigned to more than one Order - at the same time, or at different times?)

    e.g. Order #200 fulfilled by Containers 135 and 246 (which presumably between them contain the correct quantities of the Items on the Order!)

    So, there is some ambiguity about Containers above, otherwise seems reasonable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •