Multiple tables - Will they slow down the database?

I’m new to database development although I have worked with installing scripts that use databases over the years.

I’m working on a MySQL database that has historical data. No addition to this historical data is expected although calculations on it will be performed.
We’re working with the temperatures of various cities, over a 100 year period.

Let’s say I were to have

New York
Tokyo
Paris
Mumbai

There are 8 cities in all and the minimum and maximum temperatures for each day will be stored. I’m wondering if I should separate this data into different tables (1 for each city) or keep them in the same table. At first it seemed obvious to me to store them in the same table but then there are some possibilities unique to each place. For instance, there’s a field for ‘Snow’ that is marked as yes or no. This becomes redundant if the data relates to Mumbai, where it never snows.

When I select data from a database, I’m connecting. Would it be be more efficient to select from 8 tables, each of which has fewer records (1 city x 100 years) or 1 table that has more records (8 cities x 100 year data). I could create a subset table maybe for the redundant fields. In terms of performance, what would be better?

This is probably a very basic question that any database developer is expected to know. I’m very new to this and want to design the db well. Appreciate some help. Thanks!

Thanks fellas. That’s very helpful!

I would start with one table (sort of), like this and adjust it as your data would necessitate.


create table city_weather
( id int not null auto_increment
, city_name varchar(99) not null
, min_temp int
, max_temp
, temp_unit varchar(10) not null
, if_snowed int not null default '0'
, if_rained int not null default '0'
, if_dry int not null default '0'
) engine=innodb default charset=utf8 collate=utf8_unicode_ci;


If you are going to need to perform calculations on country data - where the cities data will be merged, you might want to think about having a countries table - maybe even continents - and adding a foreign key col to the above table to relate to a country

eg


create table countries
( country_name varchar(99) not null
) engine=innodb..... 

and then in city_weather do this


alter table city_weather
add constraint city_country_fk
  foreign key (country)
    references countries(country_name) on update cascade on delete cascade 

You may decide to use different datatypes for if_snowed etc coz I can’t think what they really ought to be.

hth

bazz

Personally I would have one table with all cities and a sepperate table where the tempertures etc. will be stored with in this table a foreign key city_id to join this table with the cities table. But it depends on many things.

In the sticky posts on the top is a very good post about Database design & normalization: Maybe you can get some ideas from there:

http://www.sitepoint.com/forums/showthread.php?t=63582

one table, not one per city