Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    Unanswered: Circular Reference Detection

    Ok this one has me on hold. I have a situation I haven't yet coded before and I would like your input as to the best way to go about achieving my goal.

    The basic goal is ITEMs and ASSEMBLIES.

    ---------
    ITEM
    ItemID
    Description
    etc.
    ---------

    ---------
    CONSTRUCTION
    BelongsToItemID (part primary)
    RefersToItemID (part primary)
    Qty
    ---------

    The idea being that any ITEM can have many CONSTRUCTION records that will spell out how to assemble that item. IF there are no CONSTRUCTION records for a given item, then that is a supplied item and is not constructed, it rather is a base-product. IE:

    ITEM:
    1 Zip Runner
    2 Zip
    3 Canvas
    4 Canvas Bag
    5 Special Canvas Bag
    6 Canvas Bag Delux

    CONSTRUCT:
    4 - 3 - 4 (Canvas bag contains 4 metres of Canvas)
    4 - 2 - 1 (Canvas bag contains 1 zip)
    4 - 1 - 2 (Canvas bag contains 2 zip runners)
    5 - 4 - 1 (Special Canvas Bag contains one Canvas bag)
    5 - 4 - 1 (Special Canvas Bag has an extra zip)
    6 - 5 - 1 (Canvas Bag Delux contains one Special Canvas Bag)
    6 - 3 - 2 (Canvas Bag Delux contains one additional zip)

    With me? Great! That's the first hurdle

    The problem is that I want to prevent users from creating circular references.

    IE

    CONSTRUCT:
    4 - 6 - 1 (Canvas Bag contains one Canvas Bag Delux) which doesn't make sense since it would mean that the Canvas Bag Delux contains a Canvas Bag and the Canvas Bag contains a Canvas Bag Delux.... Circular Reference. I need to detect circular references on the data entry form.

    I have a longwinded thought of using some pretty awkward VBA to go looking through the data, but the recursive nature of doing that has me at a halt while I consider options.

    How would YOU tackle the problem?

    Any insights will be greatly appreciated.

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I might not have thought this through enough however....

    For declarative integrity (no VBA required):
    ---------
    ITEM
    ItemID

    HierarchyLevel (Integer indicating level in hierarchy. I would enter multiples of 10 or so to allow inserting new items into the middle of a hierarchy)
    Description
    etc.
    ---------

    Unique index on (ItemID, HierarchyLevel)

    ---------
    CONSTRUCTION
    BelongsToItemID (part primary)
    RefersToItemID (part primary)

    BelongsToHLevel
    RefersToHLevel
    Qty
    --------

    Constraint: BelongsToHLevel >= RefersToHLevel

    I'm assuming Access allows foreign keys to be declared on superkeys, otherwise swap the unique index & Pk. I'm also assuming that an items position in the hierarchy is absolute.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I'd have a billofmaterials or gozinto table which handles that relationship. I'd then run a piece of SQL which checks to make sure that where a part was specified as a component in that assembly (and parent assemblies) its not also specified as an assembly.

    ie for one blah, you need the following blahlettes

    the tricky part is going to be identifying parent assemblies to match against.. the SQL may(!) get a little hairy.

    I wouldn't encode quantity into the item reference, as you are then going to have difficulties should the design change over time. worse you are creating an alternative which people outside the system may use. thats a recipie for disaster

    there are coding structures for part numbers elsewhere, but they are mor eoften targetted at machined/manufactured components.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sorry guys, but I can't see how these help. I'm not even sure if it is me being simply unable to understand what you're saying or whether the suggestions just don't help.

    Perhaps I have worded it wrong? Any item can become an assembly by simply adding CONSTRUCTION records to it. There are in fact hierarchy levels already, but they are not that relevant -- it is possible that a level 4 assembly can be included in a level 2 construction, but that just makes the level 2 construction a level 5 one.

    I wouldn't encode quantity into the item reference, as you are then going to have difficulties should the design change over time.
    There is currently no goals for this database to track design changes over time at all, so this isn't a problem, however, your point is acknowledged: thanks

    worse you are creating an alternative which people outside the system may use. thats a recipie for disaster
    What do you mean by that? Do you mean other users of the database will be able to stuff it?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bump... any further advice?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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