Demographic database design help

[lucatoni]

Hello,
i'm designing a demographic database in which i have to register information about cities, counties and population. In details:

- each city has its name, county in which it is, a unique ID,
TotalMen, TotalWomen, TotalPopulation
and the details about population organized in the following way:

+ for each age, starting from 0 to 100 there is a TotalMen,
TotalWomen, and a Total field:

see the attched pics for an example (in the pic: eta' is age, Totale Maschi is TotalMen, TotaleFemmine is TotalWomen and Maschi + Femmine is TotalPopulation)

these datas are taken from various .cvs files

Now my problem is how to add the details of the population..... I thought to start with this structure:

City
ID (PK)
Name
County
TotalMen
TotalWomen
TotalPopulation

and put in a separate place (tables, another database, ....) the details because the queries involve most of the time the TotalMen, TotalWomen and TotalPopulation (i know that in this way is not that normalized but it would keep simpler and faster the queries but any suggestion is very welcome). Don't know well how to proceed, considering that there are 9 counties and a total of 390 cities so i have to memorize 390 tables like the one you saw before...... i thought also to divide the database in counties but the same problem pop up..... how to organize the age table details......
What do you think? thx in advance for all :) .

[mohamedfoad]

Hello Lucatoni,

I think you should start with stating your entities and attributes related before you can decide, you need to make more analysis as the calculated fields should not stored in the database.

You need to organize the thinking as a county shall have many cities and a city will have some properties like the ID and some other details you may choose then you need to relate the cities to the population if you intend to allow the user to enter Persons names and gender after so you need to calculate the men population and women population or whatever you like.

and if you intend to store the populations per city or for each city
i think you can try to think in that manner a county is having many cities and a city belongs to one county so the relationship is one county to many citeis so a table for counties and a table for the cities having a field as FK of the countyID you created also the city table may have the other fields like toal population and total number of men and so on ...etc.

i hope i made it clear and give you a start point.

[almostbob]

A good place to start is the government bureau of statistics, whatever they may be called in the region of interest, the information requested is often available for access. premade to the date of the last census, on cdrom or downloadable

[lucatoni]

thx for the suggestion.... but i think i have to explain me better.....
in short what i have to do is to take the datas from the government bureau of statistics of my region (Sicily, Italy) and create a database from it.
The informations are in .cvs files with these fields:

ID, City, County, TotalMen, TotalWomen, TotalPopulation

these are the only datas i have to put in the db, plus the details of age of population per city... i don't have to put names of persons or other things.
As suggested by mohamedfoad i can delete the TotalPopulation field even if i think it's better to leave there considering the type of queries (involving the TotalPopulation) i have to submit to the database, ex:

+ name and ID of the cities in a X county with number of population
> Y

+ name and ID of the cities in a X county with number of population
Y<num<Z

+name, ID, county of the cities with number of pop > Y

and so on

In these cases i think it's better to have these values already present avoiding the calculation, even because i have no problems of space but i want the database to be fast and accurate.

[almostbob]

single table, as the data is presented in a single table in the csv values from stats sicily, with unique id key, the other columns can be anything at all

/* queries on city, */
select * from table where name = '%s' ,$ciity
/* queries on county */
select  countmen as count totalmen, countwomen as count totalwomen countall as count total populationfrom table where county='%s', $county // add all city in county
/* or if the subtotals are provided for counties in the imported csv */
select * from table where ( county = '%s'  and name = '' ) ,$county  //no city name = county total

BOS Aus, supply csv with
id city region state
if city is blank the row is a region subtotal
if city and region are blank the row is a state subtotal

the ID key of the city table, can be the fkey to the ages table

[mohamedfoad]

untill now i don't get your point and what is upsetting? anyway what exactly is the details of the population and should it relate to a city? or you may want to say the population is some calculations that should be ran out on the fields against some criteria? ok how about one more column of comment for some free text details!