Re: data design optimization

[BattleMan BattleClan]

there you go, i would appriciate any help



budgetactivities CREATE TABLE `budgetactivities` (
`ID` int(11) NOT NULL auto_increment,
`ActivityGroupID` int(11) NOT NULL,
`ActivityID` int(11) NOT NULL,
`ActivityName` varchar(255) NOT NULL default '',
`ActivityDescription` varchar(255) character set latin1 default NULL,
`PeriodStart` datetime NOT NULL,
`PeriodEnd` datetime NOT NULL default '9999-12-31 23:59:59',
`Value` double(11,2) NOT NULL default '0.00',
`ActivityType` enum('income','outcome') character set latin1 NOT NULL,
PRIMARY KEY (`ID`,`ActivityGroupID`,`ActivityID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

sample data :
{1,1,0,Food,,1/1/2002,1/2/2002,-10,outcome}
{2,1,1,bread,,1/1/2002,1/2/2002,-5,outcome}
{3,1,2,milk,,1/1/2002,1/2/2002,-5,outcome}
{4,2,0,work,,2/1/2002,2/2/2002,10,income}
{5,3,0,fun,,3/1/2002,3/2/2002,-100,outcome}
{6,3,1,party,,3/1/2002,3/2/2002,-100,outcome}

a sample query would be all of the data groups [and activities] which the group set happens in january,fabuary. the result will be
{1,1,0,Food,,1/1/2002,1/2/2002,-10,outcome}
{2,1,1,bread,,1/1/2002,1/2/2002,-5,outcome}
{3,1,2,milk,,1/1/2002,1/2/2002,-5,outcome}
{4,2,0,work,,2/1/2002,2/2/2002,-10,income}

another query would be the data sets of data groups which are either income or fun related. the result will be
{4,2,0,work,,2/1/2002,2/2/2002,10,income}
{5,3,0,fun,,3/1/2002,3/2/2002,-100,outcome}
{6,3,1,party,,3/1/2002,3/2/2002,-100,outcome}

thank you