Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Location
    Near Pittsburgh, PA
    Posts
    4

    Question Enforcing Purchase Order rules with Optional Parts

    I am creating an order entry system and an Item maintenance system, and the original requirements stated there would be no optional Parts.

    Like many requirments this changed and I am wondering if how my DB structure should be changed to best cope with the changes.

    -currently the order table relates on to many to the work order table
    -and the work order table relates on to many to the Work order Items table with one row for each part that item contains

    -the Item table relates to th parts table via a relation table keyed on item id and part id

    Original format
    Order 1
    -Work Order 1
    -- Item 234
    --- Part 567
    --- Part 890
    --- Part 887
    --- Part 787

    Actual format
    Order 1
    -Work Order 1
    -- Item 234
    --- Part 567 (mandatory)
    --- Part 890 or Part 891 (either but still mandatory)
    --- Part 887, Part 888, Part 889 (optional zero or one of)
    --- Part 787, Part 788, Part 789 (optional zero or more of)

    PS I havn't even asked yet if options will be dependent on previous option choices at the rate things are going they will.

    Any help would be apreciated.

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

    Re: Enforcing Purchase Order rules with Optional Parts

    This is how it looks to me, if I have understood correctly:
    Order 1
    -Work Order 1
    -- Item 234
    --- Option 1 min=1 max=1
    ---- Part 567
    --- Option 2 min=1 max=(null)
    ---- Part 890
    ---- Part 891
    --- Option 3 min=0 max=1
    ---- Part 887
    ---- Part 888
    ---- Part 889
    --- Option 4 min=0 max=(null)
    ---- Part 787
    ---- Part 788
    ---- Part 789

  3. #3
    Join Date
    Feb 2003
    Location
    Near Pittsburgh, PA
    Posts
    4

    Re: Enforcing Purchase Order rules with Optional Parts

    On further thought option 1 and 2 may be able to be combined option 1 only has one to choose from wher option 2 has more than one choice.

    Order 1
    -Work Order 1
    -- Item 234
    --- Option 1 min=1 max=1
    ---- Part 567
    --- Option 2 min=1 max=1
    ---- Part 890
    ---- Part 891
    --- Option 3 min=0 max=1
    ---- Part 887
    ---- Part 888
    ---- Part 889
    --- Option 4 min=0 max=(null)
    ---- Part 787
    ---- Part 788
    ---- Part 789

    I believe this will solve the logic of optional and mandatory now how best to maintain the tables

    would the Item Part Rel table be the best place with the following columns?

    optionGroupID int (id the group of parts to choose from)
    min int not null
    max int nullable

    or is there a better solution?

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

    Re: Enforcing Purchase Order rules with Optional Parts

    You are right, I made a mistake on option 2.

    Looks like you need the following tables:

    Part(PartID PK, ...)
    Order( OrderID PK, ... )
    WorkOrder( OrderID, WorkorderID PK, ... )
    Item( WorkOrderID, ItemID PK, ... )
    Option( WorkOrderID, ItemID, OptionID PK, min, max )
    OptionPart( WorkOrderID, ItemID, OptionID, PartID PK, ... )

    (Whether the PKs cascade down from Order to OptionPart is up to you.)

  5. #5
    Join Date
    Feb 2003
    Location
    Near Pittsburgh, PA
    Posts
    4

    Re: Enforcing Purchase Order rules with Optional Parts

    Does this sound feasable

    Item Table (PK ItemID)
    ItemGroup Table (PK ItemGroupID, FK ItemID, min, max)
    ItemGroupPartRel Table (PK (FK ItemGroupID and FK PartID))
    Part Table (PK PartID)

    Order Table (PK OrderID)
    Work Order Table (PK workOrderID, FK OrderID)
    Work Order Items Table (PK workOrderItemsID, FK WorkOrderID, FK ItemID, FK Item GroupID, FK PartID)

    The front end interface coded in ColdFusion will offer the appropriate part selection controls on the order form based on the changes we have been discussing this will handle validation and referential integrety of the Work Order Items Table

    I did not create a single column PK for ItemGroupPartRel and relate it to the work order items because we want to store the actual ItemID, PartID, and GroupID at the time the order was saved so that any changes to groupings will not affect orders already being processed.

    This is a little different than what you had suggested but I think it will be easier to maintain a histroy for.

    let me know if it sounds appropriate?

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

    Re: Enforcing Purchase Order rules with Optional Parts

    I think so. So an item like:

    -- Item 234
    --- Option 1 min=1 max=1
    ---- Part 567
    --- Option 2 min=1 max=1
    ---- Part 890
    ---- Part 891
    --- Option 3 min=0 max=1
    ---- Part 887
    ---- Part 888
    ---- Part 889
    --- Option 4 min=0 max=(null)
    ---- Part 787
    ---- Part 788
    ---- Part 789

    ... may be associated with many Work Orders via the WorkOrderItems table?

    And a Work Order can have many WorkOrderItems, and an Order can have many WorkOrders?

    Sounds OK, though of course I don't fully understand the concepts involved.

  7. #7
    Join Date
    Feb 2003
    Location
    Near Pittsburgh, PA
    Posts
    4

    Cool I think thats got it!

    I believe that decides that now we just have do do the work.

Posting Permissions

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