Results 1 to 12 of 12

Thread: Need Help

  1. #1
    Join Date
    Apr 2013
    Posts
    6

    Unanswered: Need Help

    I'm pretty new to database design and need some help.

    I want to store a series of INTs that I can search and am having a hard time figuring out what the best solution is. Essentially an array of ints range -128 to 127. The array must be of variable length and will only contain ints.

    Example of data
    1: 0 -17 129 6 -26
    2: 13 0 24 -112 4

    I want to be able to search the data. If I searched for "-17 129" I want row 1 to show up.

    Please help.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    We don't really do arrays in database land.
    One field in a table should contain only one piece of information, not multiple.

    So what we do is create associated "groups" where we can piece together the parts of information by assigning the related parts the same identifier.

    Using your example data you would create 5 individual records for those marked "1:" and 5 further ones for the ones marked "2:".

    Here's some example code [untested]. I suggest you seriously consider the field names you use too

    I have chosen to use a string field to identify the different "groups" purely for illustration (sometimes when looking at lots of numbers it's hard to see the wood for the trees). I suggest that you use a numeric field in its place.
    Code:
    CREATE TABLE your_table (
       id char(1)
     , val int
    )
    
    INSERT INTO your_table (id, val)
      VALUES ('A', 0)
           , ('A', -17)
           , ('A', 129)
           , ('A', 6)
           , ('A', -26)
           , ('B', 13)
           , ('B', 0)
           , ('B', 24)
           , ('B', -112)
           , ('B', 4)
    Now if you wanted to perform a search to find the "group" which has both the values -17, 129 and 0 (added for illustration purposes):
    Code:
    -- Find records that have any of the values
    SELECT id
         , val
    FROM   your_table
    WHERE  val IN (-17, 129, 0)
    
    /*
    id   val
    ---- -----------
    A    0
    A    -17
    A    129
    B    0
    */
    
    
    -- Now show the record(s) that have ALL these values
    SELECT id
    FROM   your_table
    WHERE  val IN (-17, 129, 0)
    GROUP
        BY id
    HAVING Count(*) = 3 -- Number of search items (we want the record to have ALL of the values)
    
    /*
    id
    ----
    A
    */
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2013
    Posts
    6
    Okay. Makes sense.

    I guess the only issue I have is (and correct me if I'm wrong), the order of the numbers in the list must be significant. When performing a search for "-17, 129" I only want rows that contain these numbers in that specific order but it doesn't matter where in the list those two number exist as long as it's in that order.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You've introduced a new dimension to your question which requires us to change the table design to have a column that denotes the ordering of the values.

    It seems like it would be a good idea if you explain your data better, because until we understand further, the wrong design may be interpreted.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's a quick attempt complete with flaws*
    e.g. If you need to check a third|forth|etc value then you need to extend the query
    Code:
    CREATE TABLE your_table (
       id  char(1)
     , val int
     , ord int
    )
    
    INSERT INTO your_table (id, val, ord)
      VALUES ('A',    0, 1)
           , ('A', - 17, 2)
           , ('A',  129, 3)
           , ('A',    6, 4)
           , ('A', - 26, 5)
           , ('B',   13, 1)
           , ('B',    0, 2)
           , ('B',   24, 3)
           , ('B', -112, 4)
           , ('B',    4, 5)
           , ('C',    0, 5)
           , ('C', - 17, 4)
           , ('C',  129, 3)
           , ('C',    6, 2)
           , ('C', - 26, 1)
    
    SELECT *
    FROM   (
            SELECT *
            FROM   your_table
            WHERE  val = -17
           ) As x1
     INNER
      JOIN (
            SELECT *
            FROM   your_table
            WHERE  val = 129
           ) As x2
        ON x2.id  = x1.id
       AND x2.ord > x1.ord
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2013
    Posts
    6
    What I'm trying to do is store music melodies in a database using intervals.

    Twinkle Twinkle Little Star will be represented as:
    0 7 0 2 0 -2 -2 0 -1 0 -2 0 -2 7 0 -2 0 -1 0 -2 5 0 -2 0 -1 0 -2 -2 0 7 0 2 0 -2 -2 0 -1 0 -2 0 -2

    Each number represents an interval either up or down. Essentially another way of notating the song.

    Problem 1: I want to store multiple songs in the database but each song contains a different number of notes.

    Problem 2: I want to be able to search for specific patterns which will return the row containing that pattern. Example: If I searched for -1 0 -2 0 I want Twinkle Twinkle Little Star to show up regardless of where in the song it exists as long as it's that specific pattern.

    Hope that's more clear.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    MySQL has a brilliant aggregate function called GROUP_CONCAT. I'm sure we can leverage this function to provide a good solution!

    As mentioned previously, I don't have access to MySQL at the moment so this code is untested
    Code:
    SELECT id
         , song
    FROM   (
            SELECT id
                 , Group_Concat(Convert(varchar(10), val) ORDER BY ord) As song
            FROM   your_table
            GROUP
                BY id
           ) As x
    WHERE  song LIKE '%-17%129%'
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2013
    Posts
    6
    Great. Thanks for the help. I will look into it.

  9. #9
    Join Date
    Apr 2013
    Posts
    6
    I set everything up and it works pretty good.

    Question. A possible solution is to store the list as a string. What are the disadvantage and benefits (if any) in doing this?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That idea violates First Normal Form (1NF), which is a big no-no in database design.

    If you don't split out your attributes then your queries for simple questions will become far more difficult than necessary. For example: how would you find a list of songs that had middle-C repeated exactly 4 times?
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2013
    Posts
    6
    Thanks again. I guess it's pretty obvious that I'm a noob.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by devinroth View Post
    Thanks again. I guess it's pretty obvious that I'm a noob.
    Quote Originally Posted by devinroth View Post
    Question. A possible solution is to store the list as a string. What are the disadvantage and benefits (if any) in doing this?
    Noobs don't ask that kind of question!
    George
    Home | Blog

Posting Permissions

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