SitePoint Sponsor

User Tag List

Results 1 to 25 of 28

Threaded View

  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


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
  •