i have an old database with ugly tables....

[corporateboy]

I would really appreciate some database expert's opinion on this.

I'm doing an overhaul for a charity's database - I just got their old one and I'm remaking it to have more features and be faster.

The charity takes applications for assistance, then determines if an applicant meets a criteria, then approves or rejects them.

Part of the application process involves indicating a number of energy companies the applicant deals with and the region they live in.

Regions have limited funding by region.

Some energy companies serve many regions.

---

the current database has a massive (10,000 + applications) table with something like 40 fields storing each application that has been made.

So my question is - will there be an improvement in speed to bust this big table down into several (e.g. one just for application id and applicant id - one crossing the energy companies against the application etc etc) - the big table has a lot of blank entries in many places (especially for the budgetary areas).

Bottom line - will totally rebuilding it result in appreciable gains that will be worth the headache of porting all the data (it was a mission to get it from MSSQL to MYSQL and understand just what is going on with all the data in the first place).

Thoughts are greatly appreciated - i'm working on this hardcore ....

[bastien]

my guess is that there is a lot of duplicate data in the table...what you want to do is reduce that to as little as possible

for ex.

Energy companies are limited (there are only so many of them)
so create a table of companies and fill in their details.
co_id
co_name
co_address
etc

Then create a table of regions, region_id
region_name

Now map the energy companies from the above tables to match the region to the co
region_co_id
region_id
co_id

then the main table
applicant_id
applicant_fname
applicant_lname
live_in_region (region_id)
etc

now its a simple matter to figure out which companies service a particular region.

You also mentioned the speed of the application. Add indeces to match the where clause for any searches

You can place indeces on
applicant_lname
applicant_fname

composite index on
applicant_lname
applicant_fname

etc

[corporateboy]

thanks mate - that's a big help,

I have one question - what's a composite index?