Table setup to trend data captured periodically

totus · #1 (**permalink**) 10-24-08, 10:33

Can anyone give me a direction to go in?

Thank you gurus! for any love at all.

mike_bike_kite · #2 (**permalink**) 10-24-08, 10:40

Code:

create table MyDataHistory(
       data_value        float,
       time_captured     datetime
)

r937 · #3 (**permalink**) 10-24-08, 10:46

ALTER TABLE MyDataHistory
ADD CONSTRAINT every_table_needs_a_PK_mike PRIMARY KEY ( time_captured )
;

gvee · #4 (**permalink**) 10-24-08, 10:49

rotflmso !

r937 · #5 (**permalink**) 10-24-08, 10:52

lmso??? laughing your socks off?

mike_bike_kite · #6 (**permalink**) 10-24-08, 11:33

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.

r937 · #7 (**permalink**) 10-24-08, 11:35

i would love that approach, if it was warranted

gvee · #8 (**permalink**) 10-24-08, 11:45

Quote:

Originally Posted by r937

lmso??? laughing your socks off?

Got it in one

totus · #9 (**permalink**) 10-29-08, 02:53

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`);

mike_bike_kite · #10 (**permalink**) 10-29-08, 06:56

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.

totus · #11 (**permalink**) 10-29-08, 08:46

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`);

mike_bike_kite · #12 (**permalink**) 10-29-08, 10:07

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.

r937 · #13 (**permalink**) 10-29-08, 10:17

spot on with everything so far. mike

Internet Services

Web Development

Digital Marketing

Consumer Tech