DB Design

Databases
1

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

2

what is this query supposed to return?

3

Just the company_id, company_name, company_banner. I was thinking if the page_id within the companies table is such good idea after all or that I should have a separate company_pages table as well?

When I had to do all separate queries it would something like this:

Index Page:


    SELECT company_id, company_name, company_banner
    FROM companies
    WHERE page_id = 1
    OR page_id = 6

Yellow Pages:


    SELECT company_id, company_name, company_banner
    FROM companies
    WHERE page_id = 3
    OR page_id = 6

Search result Pages:


    SELECT company_id, company_name, company_banner
    FROM companies
    WHERE page_id = 4
    OR page_id = 6

And I can bring that back to one query , because since I query using Coldfusion, I can set <cfparam’s> for these values.

4

good, now let’s see…

SELECT company_id
    -- company_name does not exist
     , company_banner
  FROM companies
 WHERE page_id = 2 
   AND city_id = $city

this query will work on any city

but something tells me this isn’t all there is to it…

5

Hi Rudi,

Sorry about the company_name. it should be there. The table companies has way more information, but for this example I took it out because it’s not valuable for this functionality. So the company table is this:


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_name` varchar(64) default NULL,
  `company_banner` varchar(64) default NULL,
  `isAdvertiser` tinyint(1) NOT NULL default '0',
  `page_id` tinyint(2) NOT NULL default '0',
  `start_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I will try to make it a bit clearer. Company 1, a scuba diving school , would like to advertise. They don’t want just a banner on the activities page (activity_id = 64) but they would like to have a banner on the index page as well (page_id = 1). On the index page are only banners from companies with page_id 1 or 6 (see my previous page).

Company 2 ( also a scuba diving school) would like to have an additional banner as well but they would like that banner on the cities page (page_id = 2) and not just any city. The city where the are located (companies tbl city_id) . But not every company will choose the cities page for an additional banner.

What I try to do is to make one query for the different page_id’s and that only a certain part of the query will be executed depending on which page you are. Like I said I use Coldfusion, so I try to combine it, if even possible, in a component which I would like to call from every page.

One question about your query! Where is the $city in

AND city_id = $city

coming from?

Thank you in advance rudi

6

sorry, that should be #city# and not $city :blush:

would you repeat what the query should do?

you’re going to feed the query what? a city? a company? a page?

7

The important info is coming from the company table (company_id and company_banner), but the banner will be displayed on different pages depending on which additional page or pages the advertiser has chosen. So in three situations (index, yellow pages and search results) the query is feeded with company information and page information. On the city page this is extended with city_id and on the activities page self it is extended with category_id.
Example index.cfm:


<cfquery name="getBanners" datasource="#Application.dsn#">
	SELECT company_id, category_id, city_id, company_name, company_banner, page_id
	FROM companies
	WHERE( page_id = <cfqueryparam cfsqltype="cf_sql_integer" value="6" />
	OR page_id = <cfqueryparam cfsqltype="cf_sql_integer" value="1" />)
</cfquery>

Example activities.cfm


<cfquery name="getBanners" datasource="#Application.dsn#">
	SELECT company_id, category_id, city_id, company_name, company_banner, page_id
	FROM companies
	WHERE  activity_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( Url.id )#" />
	OR ( page_id = <cfqueryparam cfsqltype="cf_sql_integer" value="5" />
    AND category_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( Url.category )#" /> )
</cfquery>
8

i’m falling further into the Great Black Hole of Confusion

your last example shows a cfqueryparam value fed to the activity_id column, but that column does not exist in the companies table

so, i’m like, lost as to what you’re after, sorry

9

I couldn’t have said it any better :rolleyes: And would have been, if not more, as confused as you are.

I saw your last reply, just before going to bed. The activities page (activities.cfm) is where the table company_activities comes in play. Url.categories is part of the menu as is Url.id:


<li><a href="#void" id="activities">Activities</a>
    <ul class="sub">
    <cfoutput query="getNavActivities" group="category_id">
        <li><a href="##void" title="#category_eng# in the Peloponnese">#category_eng#</a>
            <ul>
            <cfoutput>
                <li><a href="activities.cfm?category=#category_id#&id=#activity_id#">#activity_eng#</a></li>
            </cfoutput>
            </ul>
        </li>
    </cfoutput>
    </ul>	
</li>

I tried the following query:


    SELECT DISTINCT c.company_id, c.category_id, c.city_id, c.company_name, c.company_banner, c.page_id
    FROM companies c
    INNER JOIN company_activities ca ON c.company_id = ca.company_id
    INNER JOIN activities a ON ca.activity_id = a.activity_id
    WHERE ( category_id = #Url.category#
    AND page_id = #5# )
    OR ca.activity_id = #Url.id#