Mysql - One large table or serveral small ones?

[neo_brown]

This is really a mysql question, I hope this is the right place to post......

Im calling some data (names addresses and telephone numbers) from a mysql database.
The data is split into regions, lets say there are 200 entries per region. My question is, should I use one table to store all the data or should I break it up and have a seperate table for every region?

[tomda]

In think you should have a table for the regions (region 1, 2, 3, etc.) and in your main table (let's say the user table), put a field that you will call user_region and put the index number of the region.

Table region
------------
index - name
1 - regionA
2 - regionB

Table user
------------
index - name - address - tel - user_region
1 - name1 - add1 - tel1 - 1
2 - name2 - add2 - tel2 - 1
3 - name3 - add3 - tel3 - 2

So that you can query for a specific region from the main table.

[Lord Majestic]

How big is the database? If its small (<100k) rows then normalisation might be a waste of effort. If number of columns is also pretty low, and (I assume) regions are unlikely to change named, in which case I'd just whack the whole lot into one table and get on with it.

[neo_brown]

tomda....

That is pretty much how I have it currently, just using one table and rather than a number for the region I am using the actual region name.

Lord Majestic...

Yes it is very small, we are talking hundreds of rows rather than thousands.

Cheers guys Ill keep it all in one table.

[Lord Majestic]

Yes it is very small, we are talking hundreds of rows rather than thousands.

I'd keep it in the same table - normalisation has its merits but its overkill for extremely small tables.

[coopster]

There are (good) arguments for leaning toward db normalization as well. IMHO, normalization has never been a waste of effort, even on small tables. It's just the way I was taught and have used in practical application for many years. I've never had performance issues and it allows for growth without having to redesign and alter code for performance later. Personally, I would tend to design this in a fashion as suggested by tomda.

A quick search over the WebmasterWorld forums, or the entire Internet, will offer a great deal of discussion on the issue.

[Lord Majestic]

Most formal courses would teach people to design tables in normalised fashion. In fact this is what they expect you to do on certification courses. However in real world one should consider carefully whether normalisation is necessary, because sometimes its not. Opinions differ on when its not necessary but personally I define these cases as either very small apps (extra time spent on normalisation is disproportional to what you gain from it) with few tables or very big tables (de-normalisation is one of the easier ways to improve performance).

IMO it is far more important to separate SQL and code so that its easy to change SQL without having to go through too much code - this is good for changes from one DB to another as well as changes from poorly normalised to highly normalised tables. Changing underlieing queries is often difficult because it involves changing lots of code. If code is well structured then changing SQL to anything else that is functionally equivalent is a not a hard job.

Say in example above - why treat "region" to separate table only? Why not "names"? This could be even more useful because names linked to phones are more likely to change than region and address in which phone is located (could be mobile).

[coopster]

Granted, there are arguments for both sides here and we will differ in opinion, and that is particularly what I wanted neo_brown to realize so a specific analysis could be done for this particular situation.

Lord Majestic said:

in example above - why treat "region" to separate table only? Why not "names"? This could be even more useful because names linked to phones are more likely to change than region and address in which phone is located (could be mobile).

True. I would actually normalize the phone numbers as well ;)

One additional comment regarding the normalization route...

neo_brown said:

tomda....

That is pretty much how I have it currently, just using one table and rather than a number for the region I am using the actual region name.

A couple of points regarding using an identifier rather than the region description as key when normalizing this database.

1. Key size. If your key is a serial number such as an auto_incremented integer, then your linked tables will only have to have a small integer as opposed to storing the long text version instead.
2. If you ever want to change the region description, you only have to change it in one place, the region table.

[neo_brown]

Thank you, some very useful info there. I think Ill stick to one table for now, though I do appreciate the the pro's for normalisation. This is something I will try to implement further down the line.

[mincklerstraat]

An extra note on normalization: a contributor above noted that it's never taken away from his site's performance, and that's great. However, if you do get into a situation with a db with a number of different items of information that all have to be brought together with a complicated join that's necessary for returning a certain category of page (i.e., lists of articles in a certain topic, written by a certain author, with certain references to this and that and info on those references ... and extra info needs to be grabbed for each of these things), you can also just add an extra table that's optimized (rather than normalized) with all that information in the table. This will crank up execution speed. Downside is your admin scripts will have to be very disciplined and modify every instance of, eg., the author information in this table each time you change the author info. The mysql folks actually recommend the not-completely-normalized route when it comes to web aps that need to be fast. If you don't have a whole lot of specific queries, though, just caching the whole site would rid you of this extra work.

[Lord Majestic]

Downside is your admin scripts will have to be very disciplined and modify every instance of, eg., the author information in this table each time you change the author info.

You could have used triggers to do that (if your database supported them, which MySQL 4 aint), but getting into games with triggers may result in a major slowdowns related to updates of said normalised tables.

But lets not get too smart - for many small performance issues should be non-existant so long as you use good indices.