By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Question on database structure

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi people,

I want to migrate some old mysql-databases to postgresql.
With this step I want to optimize some database structures.

I have a (mysql-) database with all zip-codes and cities in germany.
As there are a lot of them I decided to split them in more tables at that time.
So now there are 10 tables with zip-codes (those starting with '0' in one table, those starting with '1' in another table, and so on).

I also have all streets of germany with their corresponding zip code.
Like the zip-code tables the streets are also splitted in 10 tables (same scheme as above).

My question now is, whether to keep that structure or to throw them together in two big tables?
For accessing that data it would be easier to make two tables, but I don't know what about performance (cause the street-table would have something about one million tuples)?

Thank you for your help!

Greetings,
Simon
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (MingW32)

iD8DBQE/qVWAtbkSnV7uFrYRArUDAJ9DqzANtIkgEpms2p5GGt01T27Bqw Cgj3jn
1ljUP9Dxx9t1Hvmh/3VVWvY=
=v2x8
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Simon Stiefel wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi people,

I want to migrate some old mysql-databases to postgresql.
With this step I want to optimize some database structures.

I have a (mysql-) database with all zip-codes and cities in germany.
As there are a lot of them I decided to split them in more tables at that time.
So now there are 10 tables with zip-codes (those starting with '0' in one table, those starting with '1' in another table, and so on).

I also have all streets of germany with their corresponding zip code.
Like the zip-code tables the streets are also splitted in 10 tables (same scheme as above).
My question now is, whether to keep that structure or to throw them together in two big tables?
For accessing that data it would be easier to make two tables, but I don't know what about performance (cause the street-table would have something about one million tuples)?


You are going to have nightmares for your queries then.

How much rows you will have in these two tables ?

With postgres Tables of milions rows with the right index are queried in
few millisecond.
Regards
Gaetano Mendola

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Gaetano Mendola wrote:

You are going to have nightmares for your queries then.
Well, it's ok. As far as I know the zip code I can modify my query.
But it would be better, if I don't have to think about that modification.
How much rows you will have in these two tables ?
There would be about 1,2 million rows altogether.
With postgres Tables of milions rows with the right index are
queried in few millisecond.


Ah, thats good to hear.
So I'm going to throw them together. :)

Thanks for your help!

Greetings,
Simon
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (MingW32)

iD8DBQE/qpLytbkSnV7uFrYRAqH/AJwJqWbqdcPFBV5ZPUmZBVCMgf0ANwCfYiAZ
5JVnrHBFDnN97lXxCooc008=
=5V/E
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.