SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Three small questions regarding datatypes and redundant indexes in MySQL

    First Question
    I have a table (several but for this example we'll use one) where I want to track the created timestamp and the modified timestamp for the row. When I set this up, I tried making both fields a timestamp datatype, not null and a default of CURRENT_TIMESTAMP. However, MySQL has some limitation where you can't have two columns in the same table with a default of CURRENT_TIMESTAMP. I didn't really look into it, I just figured I'd work around the issue by removing the default value from the modified field. Nope, I get errors because the default value for a timestamp datatype can not be none when not null is set. So, I decided to work around this once more and remove the not null option. This isn't ideal but it works. I'm wondering what other people do as this has to have been encountered by many of you before.

    Second Question
    I have a many to many lookup table, for example:

    Tests
    ----------
    id | name
    ----------

    Student
    ----------
    id | name
    ----------

    Lookup Table
    -----------------------------
    tests_id | students_id | grade
    -----------------------------

    Here's the problem, the grade can either be a count (eg: a number between 1-30) or it can be a percentage. Normally I'd store a count as an integer and a percentage as a decimal(3,2) but considering the grade can be either, I'm not sure what datatype to use or if this is even the proper way to go about this. Currently, I just have an integer datatype and adjust it programmatically which probably isn't ideal.

    Third Question
    I've been using SQLyog's find redundant indexes feature and I'm confused slightly about what it's finding. Here is an example:



    As you can see, I have three indexes. One for the primary key and one for each foreign key. As I understand it, in multi-column indexes, the values are indexed together, not separate. Therefore, the need for the two foreign key indexes. If that's the case, then niether foreign key should be redundant. However, as you can see it thinks I'm wrong (and maybe I am). So, if I am wrong, then technically shouldn't both foreign keys be considered redundant to the primary key?

    Thank you in advance.
    Attached Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    1. use DATETIME for both columns, and always provide a value, both created and modified

    2. use two grade columns, both nullable, instead of one

    3. suppose you have a phone book, which lists people by last name, and within last name by first name -- do you need another phone book which lists people by last name only?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. As for keeping track of time zones, should I convert everything to UTC before entering the data with UTC_TIMESTAMP()?

    2. What happens down the road if there suddenly becomes 3 ways for grades to be entered, I'd have to alter my table. Is there another way to do this?

    3. How come both foreign keys aren't considered redundant then, since both are in the primary key index?

    Thank you.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    3. How come both foreign keys aren't considered redundant then, since both are in the primary key index?
    Are you able to find all "'Ryan"'s by using the phone book?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    2. What happens down the road if there suddenly becomes 3 ways for grades to be entered, I'd have to alter my table.
    that is correct, you would


    Quote Originally Posted by Ryan Mortier View Post
    Is there another way to do this?
    yes, but in my opinion it's even messier
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Are you able to find all "'Ryan"'s by using the phone book?
    I seem to be getting confused on how it knows which foreign key is the "first name" or "last name".

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, but in my opinion it's even messier
    Alright, no problem. Do you have an article or any material I can read up on the different way?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    instead of one relationship table holding three different grade columns, three separate relationship tables, each one having only one grade column, which will always be the same type
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh wow, that is messy. Nope, won't be doing that.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    I seem to be getting confused on how it knows which foreign key is the "first name" or "last name".
    sorry to have confused you

    let's use your example instead of firstname and lastname

    here's the sample data --
    Code:
    category_id   commodity_id
    
       36            20453
       42            20735
       26            20876
       55            20945
       15            20784
       25            20599
       33            20594
       26            20735
    okay, now let's look at your indexes

    the first index, the composite index, looks like this --
    Code:
    category_id   commodity_id
    
       15            20784
       25            20599
       26            20735
       26            20876
       33            20594
       36            20453
       42            20735
       55            20945
    naturally, an index also contains a pointer (not shown) which identifies where these rows are on the disk


    the second index, the one on commodities, looks like this --
    Code:
    commodity_id
       20453
       20594
       20599
       20735
       20784
       20876
       20945
    again, each index entry contains a pointer to where the row is on the disk

    finally, the third index --
    Code:
    category_id
    
       15
       25
       26
       33
       36
       42
       55
    thus, if you were going to write a query to return something for a specific category, you could use either the first index (the composite one) or the third one

    the second index is ~not~ redundant, because neither the first one nor the third one can be used to find a specific commodity
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah thank you rudy, I definitely understand now. I wasn't aware that the order mattered.

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have another question regarding question #2.

    So I have two grade columns, one or the other will always have a value but never both.

    Therefore, is using SELECT CONCAT(column1, column2) as column3 a good way to get all values into one array?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    Therefore, is using SELECT CONCAT(column1, column2) as column3 a good way to get all values into one array?
    CONCAT doesn't produce an array, it produces a single character string

    and if (as discussed earlier) one of those columns is null, then the result will be null

    it's best to analyze the results (i.e. which column is null and which contains a value) in your application code
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect, thanks again.

  15. #15
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    Second Question
    I have a many to many lookup table
    How about:

    GRADE_TYPES
    ----------------
    id | name
    ---------------

    Lookup Table
    ----------------------------------------------------
    tests_id | students_id | grade_type_id | grade
    ----------------------------------------------------

  16. #16
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    How about:

    GRADE_TYPES
    ----------------
    id | name
    ---------------

    Lookup Table
    ----------------------------------------------------
    tests_id | students_id | grade_type_id | grade
    ----------------------------------------------------
    This wouldn't alleviate the datatype problem though, would it? I'd have to store a percent in an int type.

  17. #17
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    This wouldn't alleviate the datatype problem though, would it? I'd have to store a percent in an int type.
    Or all be just text...?
    Future grades could be 'A', 'Very Good'...

  18. #18
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, that gives me some thinking to do. I'll have to see where this project is headed in the future. It might make more sense to do it that way if there's going to be additional ways to grade these tests in the future.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    This wouldn't alleviate the datatype problem though, would it?
    that's right, it wouldn't

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's right, it wouldn't

    OK then.

    How about:

    Lookup Table
    ----------------------------------------------------
    tests_id | students_id | grade_type_id | grade_id
    ----------------------------------------------------

    and for each type of grade (int, percentage, text) there be separate tables: GRADES_INT, GRADES_PERCENTAGE, GRADES_TEXT.

    EDIT: Or maybe different partitions in the same GRADES table! This sounds better. Together with the extra null fields for each type of grade: int, percentage, char.

    The grade_type_id would point to the right grades table, and the grade_id would point to a record in that particular grades table.


    All in all, having int and percentage saved as text, and to have only one grades table would be better, I think. What I suggested first. Doing something like '85.25'*1 would implicitly cast the int/percentage value stored as text back to numeric.

    GRADE_TYPES
    ------------------
    1 | Z (http://en.wikipedia.org/wiki/Integer)
    2 | % (http://en.wikipedia.org/wiki/Percentage)
    3 | α (http://en.wikipedia.org/wiki/Alpha)

    LOOKUP
    --------------------
    203 | 158 | 1 | 8
    508 | 158 | 2 | 86.56
    1056 | 158 | 3 | B+

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by itmitică View Post
    The grade_type_id would point to the right grades table, and the grade_id would point to a record in that particular grades table.
    this is a hack, and will never allow proper foreign keys to be declared

    also, where is the actual grade stored? for integer grades, you would declare a table with 100 rows, one for each grade? and use a surrogate grade_id to point to the row for, say, a grade of 98?

    the hackishness compounds astonishingly
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the hackishness compounds astonishingly
    Exactly my point!


    Quote Originally Posted by itmitică View Post
    All in all, having int and percentage saved as text, and to have only one grades table would be better, I think. What I suggested first. Doing something like '85.25'*1 would implicitly cast the int/percentage value stored as text back to numeric.

    GRADE_TYPES
    ------------------
    1 | Z (http://en.wikipedia.org/wiki/Integer)
    2 | % (http://en.wikipedia.org/wiki/Percentage)
    3 | α (http://en.wikipedia.org/wiki/Alpha)

    LOOKUP
    --------------------
    203 | 158 | 1 | 8
    508 | 158 | 2 | 86.56
    1056 | 158 | 3 | B+

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by itmitică View Post
    GRADE_TYPES
    ------------------
    1 | Z (http://en.wikipedia.org/wiki/Integer)
    2 | % (http://en.wikipedia.org/wiki/Percentage)
    3 | α (http://en.wikipedia.org/wiki/Alpha)

    LOOKUP
    --------------------
    203 | 158 | 1 | 8
    508 | 158 | 2 | 86.56
    1056 | 158 | 3 | B+
    LOOKUP
    --------------------
    203 | 158 | 8
    508 | 158 | 86.56
    1056 | 158 | B+

    FTFY
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    LOOKUP
    --------------------
    203 | 158 | 8
    508 | 158 | 86.56
    1056 | 158 | B+

    FTFY
    Code:
    SELECT *
      FROM Lookup
     WHERE grade_type_id = 3
    vs

    Code:
    SELECT
           CASE 
             WHEN isdigits(grade) THEN NULL
             ELSE (SOME_QUERY)
           END
      FROM Lookup;

    So allow me to FTFY:

    LOOKUP
    --------------------
    203 | 158 | 1 | 8
    508 | 158 | 2 | 86.56
    1056 | 158 | 3 | B+

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
     Lookup Table
     ------------------------------------------------------------
     tests_id | students_id | grade_int | grade_dec | grade_str |
     ------------------------------------------------------------
       203    |     158     |    88     |   NULL    |   NULL     
       508    |     158     |   NULL    |   86.56   |   NULL     
      1056    |     158     |   NULL    |   NULL    |    B+
    Code:
      
    SELECT *
      FROM Lookup
     WHERE grade_str IS NOT NULL
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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