Page 1 of 1

MySQL database design questions

Posted: Tue Jun 29, 2004 8:45 am
by dagr
Hello,

I'm setting up a database and was wondering about how to optimise the design of the tables. For example, I have a user database that has about 8 key user parametres and another, say, 24 parameters that are less important (preferences, etc).

From a speed and disk space point-of-view, is there any significant difference between putting all the user parametres into one 30-column table as opposed to having 2 or 3 tables (only one of which will be indexed) with about 8 to 15 columns each?

In a similar vein, I'm also in doubt as to whether it's better having 3 separate databases: Users (private), Users' Own Data (nominally private but data gathered for anonymous global statistics) and Places (public), or just lumping them all into one big database. (The total number of tables is about 26, with an average number of 12 columns per table).

Also, does anyone know if there are freely available tables (sql or simple csv format) that list geographical data (states, regions, towns, etc, with or without map coordinates)?.

Many thanks.

David.

(PS: Sorry for the simpleton questions, but I'm new to PHP and MySQL).

Posted: Wed Jun 30, 2004 8:26 am
by lostboy
Its quicker to query one table than to join two or more tables when retrieving data. From a space perspective, if the addtional parameters are optional then I would consider another table. But performance is more likely the greater issue as the tables grow and disk space is cheap.

I would also keep them all in one db. No real gains to made in separation. But that does depend on the sensitivity of the info and the business requirements.

google for the geo data...its available

Posted: Thu Jul 01, 2004 5:08 am
by dagr
Many thanks for the reply.

(I also realize that some low-cost web hosters charge more for extra databases, so I'll stick to just one big database).

what is the URL for google geo data

Posted: Thu Jul 01, 2004 5:09 pm
by Calimero
Please what is the exact URL for geodata tables

Thanks Ahead !

Posted: Thu Jul 01, 2004 5:18 pm
by feyd
Calimero, try google. :roll:

Posted: Wed Jul 07, 2004 11:45 pm
by tbp105
Read up on database normalization. It's a little too complex to go into detail here. But basically you break it into other tables if you are repeating data or storing dissimilar data. For example, you might not want to store preferences with login data or contact information. However one rule of thumb that I'd suggest and doesn't really agree with normalization rules is to never have your primary key actually store data. Use an auto increment as your primary key. Integers will almost always be faster to query against. Also if you use the primary key to store data, you will eventually want to change it and it will require you to change all references to it. A case I have seen show up many times is usernames in the form first initial followed by last name. Suppose somone gets married and they change their name, if you used the username as the primary key, you have to change every reference to it. If you used an auto increment as the primary key, you just change the username value.