database design

[bilibytes]

hi,

I am trying to figure out what DB design to have for my application.

ok lets imagine the following enterprise: TheBigBigEnterprise Inc. => TBBE

TBBE is an international enterprise(with offices in 1000 cities) that has LOTS of employees. The employees are in charge of maintaining the commercial relations with partner enterprises in their city.
That is why, the employees invest their time in visiting these other enterprises to see how wealthy they are, and then they rate the evolution. (at each meeting TBBE employees will make a rating for the period between previous meeting and this one)

These employees have access to a website where they have their personal space.

That space is compound of a big calendar, which is used as a "to do" list.
This calendar shows the actual month, and in each day, are listed the enterprises that TBBE employees have to visit that day.

So TBBE DataBase has all the names of its partner enterprises. These partners have a space in the web, where they can request for a meeting with TBBE employees.
Only partner enterprises can ask for a meeting, that is to say they have to login first..., TBBE has also in its DataBase the partners name, password, description, logo...
When the partner enterprise has requested for a meeting, the TBBE employees can see on their calendar the partner enterprise's name and logo in each day a meeting has been requested.

the Director tells afterwards which employee will go to what meeting,
the employe will then go to the calendar and click on the day his boss told him to go. This will open another page with all the meetings for that day. And now he has to find the name of the partner enterprise he has to meet. difficult task...

The bad thing in all this, is that, these employees are a bit fed up seeing in their calendar, (and after that in the final selection page) the names of all the partners from all other cities TBBE's international employees have meeting with.
they would prefer just being shown the meetings in their own city. It would make the calendar a bit clearer. and their life too.



->how would the DB have to look like?

ok so what i have thought in my firs aproach is:

0. Employees
-all employees information

employee_id
name
pass...

1. Location
-there are repeated city names in the world, so also store the country:

CREATE TABLE `locations` (
  `location_id` int(10) unsigned NOT NULL auto_increment,
  `country` varchar(40) NOT NULL default '',
  `city` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`loc_id`)
)

2.Partners
-assuming partners may have one name per city (in the case they are an international company, they may have the same name in different cities) so pair the name with the location_id

CREATE TABLE `partners` (
  `partner_id` int(10) unsigned NOT NULL auto_increment,
  `location_id` int(10) unsigned NOT NULL default '0',
  `partner_name` varchar(255) NOT NULL default '',
  `partner_pass` varchar(255) NOT NULL default '',
  `partner_description` text NOT NULL,
  `logo_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`pre_id`)
)

3.Logos
-the images are stored separately and associated to the partner with the logo_id (should i store the partner_id in images table or the logo_id in partners table?)

CREATE TABLE `images` (
  `logo_id` int(10) unsigned NOT NULL auto_increment,
  `img_type` varchar(50) NOT NULL default '',
  `image` longblob NOT NULL,
  `img_size` bigint(20) NOT NULL default '0',
  `img_name` varchar(255) NOT NULL default '',
  `img_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`img_id`)
)

4.Calendar
-number_employees_at_meeting: for each meeting, the Director can decide to send as many employees as he wants (this is the number of employes for the specified meeting...)
-In the calendar, will also figure, the overall rating that TBBE employees have been giving of partners performances from previous meetings (since they started partnership: shall i make a specific table rates or i should store the cumulated rate with the number of rates in the calendar table, in order to, then, divide the cumulated_rate by the number of rates(->average rate) )

CREATE TABLE `calendar` (
  `calendar_id` int(10) unsigned NOT NULL auto_increment,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `partner_id` int(10) unsigned NOT NULL default '0',
  `number_employees_at_meeting` tinyint(5) unsigned NOT NULL default '0',
  `partner_rate_cum` int(10) unsigned NOT NULL default '0',
  `partner_rate_times` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`cal_id`)
)

5.Employee at Meeting
- the boss has to be able to see which of his employees he sent to what meeting, when he selects the date and partner in the calendar.
(should i put all this information in the same table as calendar or is it well done separate like this?)

CREATE TABLE `employee_meeting` (
  `employee_meeting_id` int(10) unsigned NOT NULL auto_increment,
  `calendar_id` int(10) unsigned NOT NULL default '0',
  `employee_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ppd_id`)
)

ok, so if the boss or any one in the company wants to see which employees were sent to the specified meeting, wouldn't there be too many queries?

public function list_employees_for_meeting($location, $date, $partner){
 
    //$location = array(country => usa, city => baltimore);
    /*1 Get the location id*/
        $sql = "SELECT location_id FROM locations WHERE country = $location[country] AND city= $location[city]";
    //assuming i get location_id in $location_id variable 
 
    /*2 Get the partner's id */
        $sql = "SELECT partner_id FROM partners WHERE partner_name = '$partner' AND location_id = '$location_id'";
    //assuming i get the partner_id in $partner_id variable
 
    /*3 Get the calendar_id */ 
        $sql = "SELECT calendar_id FROM calendar WHERE partner_id = $partner_id AND date = '$date' ";
 
    /*4 Get employees ids*/
        $sql = "SELECT employee_id FROM employee_meeting WHERE calendar_id = $calendar_id";
 
    /*GOT MY LIST OF EMPLOYEES*/
}

aren't that too many queries for a simple list?
lets say 100 millions of employees request this information per day, wouldn't it break the server?? if this was done like this, should i contract a big big hosting plan what is the weight of this type of queries?
is there a better way to perform this:
or by redesigning the database or by making a different query or by I don't know?

congratulation if you red this far. :)

Please you experts help me.

Thank you very very very much,

Bili