Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2008
    Posts
    59
    Thanks
    5
    Thanked 4 Times in 4 Posts

    Brainstorming: Possible multi-select query to single query?

    Brief description of tables:

    part table: id, nameid and ids of parts. if there are multiple sub-parts for a part, then the name appears multiple times, each time with an id of the sub-part. So it's easy to find the complete list of sub-parts for a part.

    eg:
    a,b,c
    1,1,1
    2,2,2
    3,1,3
    4,2,3

    where 'a' is the auto-increment field for the part table
    'b' is the part id
    'c' is the name id.

    So when a part is entered (eg. 'part1'), a list of sub-parts that make up that part is presented (assume 'part1' = 3, so 1 & 2 are presented, showing that subpart1 and subpart2 make up that part), then user can click on each sub-part to get all the information.

    Now management want multiple part numbers which are exactly the same set of sub-parts. This is easy, we can extract sub-parts from a previous part which is setup and apply them to the new part:

    5,1,4
    6,2,4

    However, in the future if another sub-part is added to a part, it will not be automatically added to the other parts which are identical is all but name.

    Since these additional parts are basically just aliases of existing parts, we can create another table (lets call it aliases) which matches a name id to a part id (so we lookup partid4 directly), or one name to another name (nameid4=nameid3, then lookup nameid3 in the part table). partid lookups will be quicker as the auto incremented column is indexed and unique, and I can see some time in the future, management wanting a part to be the contents of another part plus some sub-parts.

    So, lets ignore names for now, if we change the first type like so:
    a,b,c
    1,1,1 [sub part 1]
    2,2,2 [sub part 2]
    3,1,3 [part id 3 contains sub part 1]
    4,2,3 [and also sub part 2]
    5,5,4 [sub part 3]
    6,3,5 [part id 6 contains part id 3]
    7,5,5 [plus sub part 3]

    That looks nasty doesn't it? Referencing an id that just points to other, and doesn't even include all of them.

    I know I can solve this problem with multiple select queries and loops in php, but I really want as much as possible in the sql side and less eating cpu cycles parsing php code.

    Any ideas from other people? I'm thinking there must be an easier way.

    I can make slight changes to the database and structure, along with code alterations, but don't really want to rewrite the entire system to use it.

    One last thing: The server is MySQL 4.1.
    Last edited by scrappy; 05-21-2009 at 08:44 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Yeah, the classical "tree" problem.

    And I think you goofed in your "tree" presentation. If "a" is an autonumber field, how'd you get duplicate values???

    Let's rewrite that a bit more meaningfully. Let's use the partname in place of the nameid, for clarity. And if a part doesn't contain any other parts, try using NULL instead of duplicateing the autoid field:
    Code:
     autoid :: contains :: partname
          1 ::     null :: subpartA
          2 ::     null :: subpartB
          3 ::        1 :: partX  [partX contains subpartA]
          4 ::        2 :: partX  [partX contains subpartB]
          5 ::     null :: subpartC
          6 ::        3 :: partY [partY contains subpartB]
          7 ::        5 :: partY [partY contains subpartC]
    Do I have that right??

    Now a question: With this system, I don't see any way to have sub-sub parts. Is that okay? Is that intended??

    There *are* other ways to organize the data so that you can have multi-level trees, but I don't see a way to do that with this system.

    Is it possible that you were trying to say that your column "a" is a *FOREIGN* key to another table with part number/names???

    Hmmm...but then what's the point of your column "c"???

    I'm a bit lost.

  • #3
    New Coder
    Join Date
    Nov 2008
    Posts
    59
    Thanks
    5
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by Old Pedant View Post
    Yeah, the classical "tree" problem.
    And I think you goofed in your "tree" presentation. If "a" is an autonumber field, how'd you get duplicate values???
    Yes, I goofed.
    if a part doesn't contain any other parts, try using NULL instead of duplicateing the autoid field:
    Good idea. That would make more sense.
    Do I have that right??
    Yes, perfect.
    Now a question: With this system, I don't see any way to have sub-sub parts. Is that okay? Is that intended??
    At the moment, yes. However, management already want aliases, so they can have parts where the only difference is the name of the part. I can see not too long in the future for them to want sub-sub parts. I don't see it going any further than that however (sub-sub-sub is likely never happen).
    There *are* other ways to organize the data so that you can have multi-level trees, but I don't see a way to do that with this system.
    Neither do I, so I appreciate your comments on how you would go about doing such a tree system.
    Is it possible that you were trying to say that your column "a" is a *FOREIGN* key to another table with part number/names???
    No, column A is simple auto-number, I just goofed when I typed the table up above. Column C is indeed the id of the name.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Okay, for starters, I'd just ditch the autonumber column.

    It isn't doing anything useful and I can't see any query that would ever depend on it.

    And then I'd just have the table look like:

    Code:
    partID  : subPartID
    Where *both* those id's refer into the "part names" table.

    Again, for clarity, I'm going to use names in my example table instead of numbers.

    So:
    Code:
    TABLE NAME: assemblies
    partID  :  subpartID
    partX    : partA
    partX    : partB
    partY    : partB
    partY    : partC
    partXXX  : partD
    partXXX  : partY
    So that means the partXXX is really
    Code:
    partXXX
         partD
         partY
             partB
             partC
    That is, we now handle sub-sub parts.

    And now parts that are *ONLY* used as sub-parts are *NOT* shown in this table.

    If they need to kept in another table (for what??), fine. But they don't appear in this table.

    To find all the sub-parts *AND* sub-sub-parts that make up one assembly, your query looks like this:
    Code:
    SELECT 1 as ordering, partID, subPartID FROM assemblies
    UNION
    SELECT 2, a1.partID, a2.subPartID 
    FROM assemblies AS a1, assemblies AS a2
    WHERE a1.subpartID = a2.partID
    ORDER BY partID, ordering, subpartID
    For the above table (and again using names instead of ids) that would give us:
    Code:
    1  :: partX   :: partA
    1  :: partX   :: partB
    1  :: partY   :: partB
    1  :: partY   :: partC
    1  :: partXXX :: partD
    1  :: partXXX :: partY
    2  :: partXXX :: partB
    2  :: partXXX :: partC
    And, yes, it could easily be extended to sub-sub-sub parts. Past about 4 levels, though, I'd go with a different scheme.

  • Users who have thanked Old Pedant for this post:

    scrappy (05-22-2009)


  •  

    Posting Permissions

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