MySQL database design questions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dagr
Forum Newbie
Posts: 2
Joined: Tue Jun 29, 2004 8:45 am

MySQL database design questions

Post 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).
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
dagr
Forum Newbie
Posts: 2
Joined: Tue Jun 29, 2004 8:45 am

Post 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).
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

what is the URL for google geo data

Post by Calimero »

Please what is the exact URL for geodata tables

Thanks Ahead !
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Calimero, try google. :roll:
tbp105
Forum Newbie
Posts: 2
Joined: Wed Jul 07, 2004 11:16 pm

Post 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.
Post Reply