MySQL Forums
Forum List  »  Optimizer & Parser

Re: data design optimization
Posted by: BattleMan BattleClan
Date: March 30, 2006 01:41PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2781
March 30, 2006 07:44AM
1814
March 30, 2006 09:02AM
Re: data design optimization
1981
March 30, 2006 01:41PM
2416
March 30, 2006 01:43PM
1915
March 30, 2006 04:03PM
1785
March 31, 2006 04:59PM


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.