Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question on database structure

Question on database structure

From
"Simon Stiefel"
Date:
-----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-tablewould have something about one million tuples)? 

Thank you for your help!

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

iD8DBQE/qVWAtbkSnV7uFrYRArUDAJ9DqzANtIkgEpms2p5GGt01T27BqwCgj3jn
1ljUP9Dxx9t1Hvmh/3VVWvY=
=v2x8
-----END PGP SIGNATURE-----

Re: Question on database structure

From
Gaetano Mendola
Date:
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-tablewould 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

Re: Question on database structure

From
"Simon Stiefel"
Date:
-----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-----