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
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
, 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
create table countries
( country_name varchar(99) not null
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.
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:
one table, not one per city