views:

88

answers:

3

I have to design a traffic database which includes data from different towns (8 towns) 2mb in a period of 10 min for each town 24h. The incoming data is the same for all Town. So my first question is what is better on the performance side: design one database for all towns with many tables (one table for each town) or design many databases (one database for each town)? My second question is what is the best database mangement system for this scenario, MySQL, Postgres, Oracle, or others?

Thanks in advance.

A: 

Town with many tables (one table for each town) or design many databases (one database for each town)?

I don't think that makes any differences.

Maybe take DB2. This is really fast, if you create separate Table spaces for each table.

u2ix
When the databases are on different servers, the difference can be huge!
Juergen
+3  A: 

The amount of data you are receiving each day is quite a lot (~5GB) but the number of rows being inserted is actually rather low. Consequently you need to design your physical model to make database storage adminstration easy and querying efficient.

Having a separate database per town only makes sense if you are going to have a server per database. But you do not need load balancing, as you only have to handle eight inserts every ten minutes. On the other hand that architecture will turn every query which compares one town against another into a distributed query.

Having one table per town in the same database might give you some performance advantages if the majority of your queries are constrained to data from a town rather than comparing towns. But I wouldn't like to put much money on it. Even if it did work, it might make other sorts of queries harder.

Given that the data is the same for all towns my preferred option would be one table with a differentiating column (TOWN_ID). Especially if I had the money to spring for a Oracle license with the Partitioning option.

APC
A: 

Differnt databases per town can be difficult to maintain, same with differnt tables. It might be workable if you never have to compare towns though, but sooner or later I'd bet on having to compare data from differnt towns.

Partitioning data is the way to go. Anty database which supports partioning of data such as Oracle or SQL Server would work fine. Not sure if Postgre or Mysql support this, you'd have to ask someone more familiar with those databases.

HLGEM