Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    4

    Unanswered: Database Design structure

    Hi all,

    What is the best table layout for a "mechanical parts" inventory database with the following problem (I can do the inventory db, but don't want to head down the wrong road for this other part):

    Problem:
    The functional location(s) of the part can be stored as an attribute of the part. A part can be component of another part, which can be the component of another part, etc.

    If a part is listed as a component of the low level part, I'd like it to automatically become a child of its parent's parent parts too.

    I hope that makes sense, I'm looking for feedback on table structuring.

    Thanks in advance,
    Pd
    Last edited by P-duck; 10-02-06 at 16:01.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    ick, recursion. There are quite a few theories on how to set this up. Google really is the best place to do the compare and contrast. Lookup database recursion, there's a TON of stuff there.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I don't think this is too terrible to store the data. It sounds like a heirarchy of sorts. The classic example is creating an organzational chart for a company using an Employee table. Everyone working for a company is an employee of that company, but how do you show the hierarchy of the management. I have seen it where you add a ManagerID field and everyone has the EmployeeID of their manager in the ManagerID. The only person that doesn't have a ManagerID would be the President/CEO.

    I think this structure would work for you. But when you select a part you will need a function that will recursively find all the related parts. The storage of the data is probably going to be easier than the function(s) to make sure the user is getting all the applicable parts.

  4. #4
    Join Date
    Oct 2006
    Posts
    4
    DCKunkle,

    Thanks for replying. Your suggestion is very attractive and I would like to do it that way. The only thing I can't wrap my brain around is querying multiple 'managers' up or listing 'employees' in a partitioned/indented fashion.

    Can you point me in the right direction on that?

    Thanks again.
    Pd

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Might as well add that I have implemented neither of these.

    Rudy Tuesday's take on the adjacency model:
    http://sqllessons.com/categories.html

    Celko's Nested Set Model:
    http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's kind of what I was getting at... Sucking the data back out is a topic of much debate that can be better answered by the numerous published schemas available on the web.

    another good item to look for is anything dealing with Bill of Material's (BOM). By definition they tend to involve complex recursive relationships.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2006
    Posts
    4
    Thanks for everyone's feedback. I've read the posts and links provided and did some searching around - I can't believe how difficult this problem is.

Posting Permissions

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