I try to set up a banner advertisement system, but I’m suddenly not sure If the design of the database is how it should be since I can’t figure how my query should look like.
The following tables are involved:
CREATE TABLE IF NOT EXISTS `companies` (
`company_id` int(6) unsigned NOT NULL auto_increment,
`category_id` int(3) NOT NULL,
`city_id` int(3) NOT NULL,
`company_banner` varchar(64) default NULL,
`isAdvertiser` tinyint(1) NOT NULL default '0',
`page_id` tinyint(2) NOT NULL default '0',
PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE IF NOT EXISTS `activities` (
`activity_id` int(4) NOT NULL auto_increment,
`activity` varchar(32) default NULL,
`activity_text_eng` text,
PRIMARY KEY (`activity_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `company_activities` (
`company_id` int(6) unsigned NOT NULL,
`activity_id` int(6) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `cities` (
`city_id` int(3) NOT NULL auto_increment,
`county_id` int(3) NOT NULL,
`city_eng` varchar(12) default NULL,
`city_gr` varchar(12) default NULL,
`city_text_eng` text,
`city_text_gr` text,
PRIMARY KEY (`city_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `pages` (
`page_id` int(2) NOT NULL auto_increment,
`page_name` varchar(15) NOT NULL,
PRIMARY KEY (`page_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
INSERT INTO `pages` (`page_id`, `page_name`) VALUES
(1, 'Index'),
(2, 'Cities'),
(3, 'Yellow Pages'),
(4, 'Search Results'),
(5, 'Categories'),
(6, 'All Pages');
By default the banner will appear on the activities page, which is a completely dynamic page. An advertiser can choose to have a banner on additional pages (see table pages and page_id in table companies) and this is where I loose the plot some how. I would like to create a query which is usable on every page, instead of having a separate query for every page but can’t figure how It should look like?
Note: the Categories page_id (tbl pages row 5) is for companies who are for example a watersport company and would like to have a banner on every water sport related activity
Any help would be more than welcome