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 8 of 8
  1. #1
    New Coder
    Join Date
    Jun 2009
    Posts
    17
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Database Structure Help!

    Hello All...

    Just wanted some advice...

    I have some Data on PartNumber supercessions which i'd like to store for future use

    data is essentaly...

    Part1 --> Part2 -->Part3 -->Part4

    length of chain varies..

    I want to be able to store this... but also highlight the "latest" Part Number..

    so essentialy im able to query my database.. say, Part2, the answer i end up with is Part4


    im thinking..
    Table.

    ChainID / Part / Latest
    001 Part1
    001 Part2
    001 Part3
    001 Part4 Y


    essentialy i could query the Part - get the chainID, then get the latest Part? (y)


    does this structure make sense - or is there a more effective way of storing

    this will eventualy contain around 200k part numbers, say 75k "chains"

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    I don't understand what makes "Part4" be the "Latest".

    Or are you saying that "Latest" *is* a field in the DB?

    If so, then your table structure is just fine. You don't have to "query the Part".

    Just do:
    Code:
    SELECT T2.* FROM table AS T1, table AS T2
    WHERE T1.ChainID = T2.ChainID
    AND T1.Part = 'Part2'
    AND T2.Latest = 'Y'
    Yes, it works even if you are looking for 'Part4'.

    Just a simple "self join".

    Naturally, you would need ChainID to be indexed for decent performance. And you'd probably want Part to be indexed as well.

    If all the values in Part for a given ChainID are different, then in fact you could make the combination of ChainID and Part be your primary key, and then it would be really efficient.

    Come to think of it, even if they aren't unique, you still might get better performance using a non-unique composite key, combiing ChainID and Part.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Comment: If you ever might want to be able to reconstruct the ordering of your chain, you might consider using
    Code:
    ChainID / Part / Sequence
        001 / Part1 / 1
        001 / Part2 / 2
        001 / Part3 / 3
        001 / Part4 / 4
    And then your query becomes:
    Code:
    SELECT T.* 
    FROM table AS T, 
         ( SELECT ChainID, MAX(Sequence) AS MaxSequence
           FROM table 
           GROUP BY ChainID ) AS S
    WHERE T.ChainID = S.ChainID
    AND T.Part = 'Part2'
    AND T.Sequence = S.MaxSequence
    Hmmm...SQL Server could optimize that, but I'm not so sure about MySQL.

    Let me think on it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Jun 2009
    Posts
    17
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for the Tips re the Primary ID.

    Do you think there is anyway building in a way of tracking the order of the parts?

    Latest was going to be an ID in the table.

    or, could i change latest to a number,

    ie

    ChainID / Part / Latest
    001 Part1 1
    001 Part2 2
    001 Part3 3
    001 Part4 4


    then modify the query to pick the highest value in "latest" against the chain ID?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    I think for MySQL using Sequence, if you wanted decent performance, you'd have to do this:
    Code:
    SELECT T.* 
    FROM table AS T, 
         ( SELECT T2.ChainID, MAX(T2.Sequence) AS MaxSequence
           FROM table AS T1, table AS T2
           WHERE T1.ChainID = T2.ChainID
           AND T1.Part = 'Part2'
           GROUP BY T2.ChainID ) AS S
    WHERE T.ChainID = S.ChainID
    AND T.Sequence = S.MaxSequence
    Which looks more complex and looks like it would be slower, but it won't be.

    See, the inner SELECT would immediately find that it only need (in our example) 4 records (the ones with ChainID = 001) and so then it just has to select the MAX from those 4. And then the join to the main T table is lightning fast, because it has already fetch that record as part of the inner SELECT!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    masterz2k (11-12-2011)

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Quote Originally Posted by masterz2k View Post
    Do you think there is anyway building in a way of tracking the order of the parts?
    LOL! Beat you to it!!

    Incidentally, if you used an auto_increment ID for the entire table *AND* the parts were entered in sequence order, then yes, you could use that in place of the Sequence field.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    masterz2k (11-12-2011)

  • #7
    New Coder
    Join Date
    Jun 2009
    Posts
    17
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for the Help! autoID would be a good way i think

    i'l have a play with getting the data into the correct format and a way of keeping it updated / amended

    Sequence wouldn't be needed immediately, but i like to plan ahead to in the future it might well be needed, the AutoID would future proof this as well as keeping the "latest" easier, infact makes the maintenance easier as well as anything added would automacicaly be the latest... as long as the starting data was OK!

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Actually, adding another record with same ChainID but a one-greater sequence number isn't that hard.

    Example:
    Code:
    INSERT INTO table ( ChainID, Part, Sequence )
    SELECT ChainID, 'new part name', MAX(Sequence) + 1
    FROM table
    WHERE ChainID = $chosenchainid
    GROUP BY ChainID
    Or even
    Code:
    INSERT INTO table ( ChainID, Part, Sequence )
    SELECT ChainID, 'new part name', Sequence + 1
    FROM table
    WHERE ChainID = $chosenchainid
    ORDER BY Sequence DESC LIMIT 1
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there 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
    •