Help me for creating huge database in Mysql

Question

We are building a website for business on global wise, for every country major cities are covered in this concept.

I need some suggestions from PHP Mysql People.

Can i create single databse for all cities or multiple databases. Because in this system contains some relations between cities ,every chamber need nearly 50 tables for networking and some other tables.

If I can create separate databases for every chamber there would be nearly 50*1000 tables need because we have 1000 cities.

So Please give suggestions how can i build database for my system.

Thank you Ravi

Answer 1

After reading your question the only suggestion I can give you is to revise your knowledge about databases... I think you are missing something... the web can help you on this.

Anyway if you have 2 object City and Chamber you need 2 tables:

• Table1: cities
• Relation: 1-n
• Table2: chambers

Answer 2

Avoid database designs with fluid table numbers. Tables aren't rows. It doesn't make sense to have a new table for every city. Cities each have a name, a location, and a lot of other shared properties relevant to your business. It doesn't make sense to have a bunch of tables that share the exact same structure. I suggest you create a table for countries a table for cities and another table for a 'chamber'. Associate each city with a country. If you're expecting of working with thousands of cities, well that's fine. A table in MySQL can easily handle thousands of rows without using up a lot of your servers resources. If there's something unique about each city, that would require variable fields, you might want to do this differently, but there's probably never a need to create thousands of tables.

Answer 3

relational database design dictates: just one database. splitting your data up over multiple databases may be - under certain circumstances - an option for a performance hack applied by a pro who exactly knows what he does (but neither you nor i are), but normally it just complicates the situation and makes the system slower and harder to maintain.

the question is: do you even need dirty performance hacks? having thousands of cities is not a performance problem per se, it all depends on good indices, the server(s), tuning, workload, ...

Answer 4

You need to normalize what is the same, irrespective of the city. Or chamber.

One way to think of this is to look at all the distinct entities you need. Obviously 'city' is one of them. There should be no reason you can't use one table to represent all the cities. Okay, so now you can identify which city a chamber is in with one more field. So now all chambers can be in one table. This also provides a way to say a chamber belongs to only one city, but a city can have more than one chamber. This is called a one-to-many relationship.

Relationships between the same types of object (e.g. cities) is more complex. You need to define quite what the relationship is: specifically what it's called and what each side is called. If you need a many-to-many relationship, i.e a city is connected to several other cities, you will need another table to list those relationships. Exploring this is a little beyond the scope of this answer box, so I recommend you find a good book or web page about database design, specifically about normalization.