Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2008
    Posts
    14

    Unanswered: Table setup to trend data captured periodically

    Can anyone give me a direction to go in?

    Thank you gurus! for any love at all.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    create table MyDataHistory(
           data_value        float,
           time_captured     datetime
    )

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ALTER TABLE MyDataHistory
    ADD CONSTRAINT every_table_needs_a_PK_mike PRIMARY KEY ( time_captured )
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    rotflmso !
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lmso??? laughing your socks off?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Totally agree with you - all tables need a PK - I just wanted to wait until the spec for the table widened up a bit before guessing what the key might be. We could for instance want to store lot's of different data values in this table and differentiate between them using a type field. I'm guessing you wouldn't like this approach but it would mean using a different PK to just the time.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would love that approach, if it was warranted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    lmso??? laughing your socks off?
    Got it in one
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2008
    Posts
    14

    Like so...

    Code:
    CREATE TABLE `SocialSites`
    (
    `SiteID` INTEGER unsigned  NOT NULL AUTO_INCREMENT ,
    `SiteThumbnail` VARCHAR(255),
    `SiteName` VARCHAR(150) UNIQUE ,
    `SiteURL` VARCHAR(255) NOT NULL,
    `AlexaRankID` INTEGER UNIQUE ,
    PRIMARY KEY (`SiteID`)
    );
    
    CREATE TABLE `AlexaTrend`
    (
    `AlexaRankID` INTEGER,
    `AlexaRank` BIGINT,
    `DateCaptured` DATETIME
    );
    
    ALTER TABLE `AlexaTrend` ADD FOREIGN KEY (`AlexaRankID`) REFERENCES `SocialSites`(`AlexaRankID`);

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You missed the PK on the data history table. This should probably be put in with the create table statement but I'll keep with Rudy's syntax
    Code:
    ALTER TABLE AlexaTrend
    ADD CONSTRAINT look_whos_laughing_rudy PRIMARY KEY ( AlexaRankID, DateCaptured );
    Mike

    PS the fields don't need the quotes around them in your code, they are only necessary if your field names have spaces in them.

  11. #11
    Join Date
    Oct 2008
    Posts
    14
    Quote Originally Posted by mike_bike_kite
    You missed the PK on the data history table. This should probably be put in with the create table statement but I'll keep with Rudy's syntax
    Code:
    ALTER TABLE AlexaTrend
    ADD CONSTRAINT look_whos_laughing_rudy PRIMARY KEY ( AlexaRankID, DateCaptured );
    Mike

    PS the fields don't need the quotes around them in your code, they are only necessary if your field names have spaces in them.
    Thanks Mike. Below is my revision. Are you saying that a Primary is required for both AlexaRankID and DateCaptured? When creating the Foreign Key link to the table I get this...

    Code:
    ...
    ....
    ....
    `PingTimer` INTEGER,
    `DLSpeed` INT,
    `AlexaRankID` INTEGER,
    `AlexaDateCaptured` DATETIME,
    PRIMARY KEY (`SiteID`)
    );
    
    CREATE TABLE `AlexaTrend`
    (
    `AlexaRankID` INTEGER,
    `AlexaRank` BIGINT,
    `AlexaDateCaptured` DATETIME,
    PRIMARY KEY (`AlexaRankID`,`AlexaDateCaptured`)
    );
    
    ALTER TABLE `SocialSites` ADD FOREIGN KEY (`AlexaRankID,AlexaDateCaptured`) REFERENCES `AlexaTrend`(`AlexaRankID,AlexaDateCaptured`);

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm a bit confused by what you're doing now but I'd say:
    • The FK should go from AlexaTrend to SocialSites and not the other way round.
    • The field AlexaDateCaptured shouldn't be in the table SocialSites.
    • If the PK on SocialSites is SiteID then I believe this field should be used in the table AlexaTrend rather than AlexaRankID.
    • I'd still get rid of those quotes you have everywhere as they make things more complex to read and occasionally cause mistakes ie FOREIGN KEY (`AlexaRankID,AlexaDateCaptured`).

    Rudy is the FK king on this forum so I'll bow to him on the accuracy of any of the above.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    spot on with everything so far. mike

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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