Table design issue and complex query questions
Posted by: David Usherwood
Date: August 18, 2012 01:05PM

Hi All,

Please could anyone advise about the following table design and resulting select query. I am getting a partial Cartesian because the joins are not quite right. These are the tables involved with the relevant indexes and the query follows:

CREATE TABLE IF NOT EXISTS `NewpersistentCentreProfile` (
`pmcp_id` int(11) NOT NULL AUTO_INCREMENT,
`CNA_ID` int(11) NOT NULL,
`YearMonth` date NOT NULL,
`ProfileActive` tinyint(1) NOT NULL DEFAULT '1',
`NetworkTypeID` tinyint(3) NOT NULL,
`CourseID` tinyint(3) NOT NULL,
`ProfileEffectiveFrom` date DEFAULT NULL,
`ProfileEffectiveTo` date DEFAULT NULL,
`RegsTarget` smallint(5) NOT NULL DEFAULT '0',
`ComplsTarget` smallint(5) NOT NULL DEFAULT '0',
`OGSAccessTarget` smallint(5) NOT NULL DEFAULT '0',
UNIQUE KEY `pmcp_id` (`pmcp_id`),
UNIQUE KEY `CNA_ID` (`CNA_ID`,`YearMonth`,`ProfileActive`,`NetworkTypeID`,`CourseID`,`ProfileEffectiveFrom`),
UNIQUE KEY `CNA_ID_2` (`CNA_ID`,`ProfileEffectiveFrom`,`ProfileEffectiveTo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `persistentNetworkType` (
`NetworkTypeID` tinyint(2) NOT NULL,
`NetworkTypeDesc` varchar(50) NOT NULL,
`NetworkType` varchar(5) NOT NULL,
PRIMARY KEY (`NetworkTypeID`),
UNIQUE KEY `NetworkCode` (`NetworkType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `centresDataByDay` (
`CNA_ID` int(11) NOT NULL,
`dateReference` date NOT NULL,
`myguideRegistrationCount` int(11) NOT NULL DEFAULT '0',
`goOnRegistrationCount` int(11) NOT NULL DEFAULT '0',
`myguideRegsYTD` int(11) NOT NULL DEFAULT '0',
`goOnRegsYTD` int(11) NOT NULL DEFAULT '0',
`myguideOLBStartsCount` int(11) NOT NULL DEFAULT '0',
`goOnOLBStartsCount` int(11) NOT NULL DEFAULT '0',
`myguideOLBStartsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBStartsYTD` int(11) NOT NULL DEFAULT '0',
`myguideFundableOLBComplsCount` int(11) NOT NULL DEFAULT '0',
`goOnFundableOLBComplsCount` int(11) NOT NULL DEFAULT '0',
`myguideOLBCourseComplsCount` int(11) NOT NULL DEFAULT '0',
`goOnOLBCourseComplsCount` int(11) NOT NULL DEFAULT '0',
`myguideFundableOLBComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnFundableOLBComplsYTD` int(11) NOT NULL DEFAULT '0',
`myguideOLBCourseComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBCourseComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBLADCount` int(11) NOT NULL DEFAULT '0',
UNIQUE KEY `CNA_ID` (`CNA_ID`,`dateReference`),
KEY `dateReference` (`dateReference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `currentCentreFunding` (
`ccf_id` int(11) NOT NULL AUTO_INCREMENT,
`pcf_id` int(11) NOT NULL DEFAULT '0',
`CNA_ID` int(11) NOT NULL,
`parentCentreRef` int(11) NOT NULL DEFAULT '0',
`NetworkTypeID` tinyint(2) NOT NULL DEFAULT '0',
`FundingStateID` tinyint(1) NOT NULL DEFAULT '0',
`startDatetime` datetime DEFAULT NULL,
`endDatetime` datetime DEFAULT NULL,
`profile` smallint(3) NOT NULL DEFAULT '0',
`dirty` tinyint(1) DEFAULT '0',
UNIQUE KEY `ccf_id` (`ccf_id`),
UNIQUE KEY `CNA_ID_2` (`CNA_ID`),
UNIQUE KEY `CNA_ID` (`CNA_ID`,`startDatetime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `hh_Centre` (
`CentreID` int(11) NOT NULL AUTO_INCREMENT,
`CentreCode` varchar(15) DEFAULT NULL,
`CentreName` varchar(255) NOT NULL,
`PartnerType` enum('Centre Partner','Access Point') DEFAULT 'Centre Partner' COMMENT 'What type of centre is this?',
`OrgTypeID` mediumint(5) DEFAULT NULL,
`LocationID` int(11) NOT NULL,
`Website` varchar(200) DEFAULT NULL,
`OpenToPublic` tinyint(1) NOT NULL,
`WhyNotOpen` varchar(255) DEFAULT NULL,
`CentreActive` tinyint(1) NOT NULL,
`MembershipCentre` tinyint(1) NOT NULL,
`CentreFundingTypeID` smallint(6) NOT NULL DEFAULT '0',
`CreatedBy` varchar(50) NOT NULL,
`CreatedDate` datetime NOT NULL,
`UpdatedBy` varchar(50) DEFAULT NULL,
`UpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`DataSource` varchar(50) DEFAULT NULL COMMENT 'Where did we get this data from?',
`DeliverMaths3To2` tinyint(1) DEFAULT NULL,
`DeliverEnglish3To2` tinyint(1) DEFAULT NULL,
`ReceiptSFA1213` tinyint(1) DEFAULT NULL,
`SFAActivities` varchar(500) DEFAULT NULL,
`OtherFunding1213` varchar(500) DEFAULT NULL,
`AwardingBodyStatus1213` varchar(500) DEFAULT NULL,
`Partnerships` varchar(500) DEFAULT NULL,
`provideAltContact` tinyint(1) DEFAULT NULL,
`AltContactTitle` varchar(9) DEFAULT NULL,
`AltContactFirstName` varchar(100) DEFAULT NULL,
`AltContactLastName` varchar(100) DEFAULT NULL,
`AltContactJobTitle` varchar(100) DEFAULT NULL,
`AltContactEmail` varchar(100) DEFAULT NULL,
`AltContactPhone` varchar(12) DEFAULT NULL,
`doesCloseAtLunch` tinyint(1) DEFAULT NULL,
`lunchClosingTime` varchar(16) DEFAULT NULL,
`PartnershipsInfo` text,
PRIMARY KEY (`CentreID`),
KEY `PartnerType` (`PartnerType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

And this is my query that is built dynamically in Joomla:

SELECT SQL_CALC_FOUND_ROWS
"---" AS CentreName,
NetworkType,
FundingStateID,
SUM(RegsTarget) AS RegsProfile,
SUM(goOnRegistrationCount) AS goonRegs,
SUM(goOnOLBStartsCount) AS goonStarts,
SUM(ComplsTarget) AS ComplsProfile,
SUM(goOnOLBLADCount) AS goonComps,
DATE_FORMAT(dateReference, "%Y-%m") AS dateReference,
"---" AS CNA_ID
FROM ukon_dw3.persistentNetworkType AS nt,
uko_2.hh_Centre AS c,
ukon_dw2.currentCentreFunding AS cf,
ukon_dw2.centresDataByDay AS cd
LEFT OUTER JOIN
ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID AND
cd.dateReference BETWEEN p.ProfileEffectiveFrom AND
p.ProfileEffectiveTo
WHERE cd.dateReference >= "2012-07-01" AND
cd.dateReference <= "2012-08-17 23:59:59" AND
cf.NetworkTypeID IN (3) AND
cf.CNA_ID = cd.CNA_ID AND
cf.NetworkTypeID = nt.NetworkTypeID AND
c.CentreID = cf.CNA_ID AND
DATE_FORMAT(p.YearMonth,'%Y-%m') BETWEEN
DATE_FORMAT('2012-07-01 00:00:00','%Y-%m') AND
DATE_FORMAT('2012-08-16 23:59:59', '%Y-%m') AND
p.ProfileActive = 1 AND
p.CourseID IN (1)

GROUP BY DATE_FORMAT(dateReference, "%Y-%m"),
NetworkType,
FundingStateID

1). I am struggling to define the tables and resultant joins between the
cf and cd tables. The cd table possesses a compound key that hold the date i.e 365 record for each centre. The cf table doesn't possess a daily date only a centre (CNA_ID) and startDatetime colum. Hence I can't get a good join.

2).

I am also struggling to define the cd and and p joins

ukon_dw2.centresDataByDay AS cd
LEFT OUTER JOIN
ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID AND
cd.dateReference BETWEEN p.ProfileEffectiveFrom AND
p.ProfileEffectiveTo plus ancillary p cols against constants in the where clause. The cd table hold 365 record for each centre but the profile table is by month. I have added the effectivefrom and effectiveto column to try to address the cartesian like problem.
I have added a number of unique keys to the p table because I am trying to cater for future scalability such as the introduction of profiles for multiple courses at a particular centre.

Many thanks for your help.

Dave

Options: ReplyQuote


Subject
Written By
Posted
Table design issue and complex query questions
August 18, 2012 01:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.