Medium to Largeish Database Conversion Project - Thoughts or Ideas?

[aesthetics1]

Hello.

 

I work for a small family-owned copier sales and service dealership. We have a Customer Service database that is done in Access. It keeps track of all of our customers, all of the copy machines, and also holds all of our open and old (completed) service calls.

 

The problem is that it is beginning to really slow down. I don't know much about access.. I know that the database is extremely old, and not very functional or intuitive and that I want to update it or completely re-design it.

 

I've begun work on a php/mysql based version of the database. I have already copied all of the data using a tool from the access tables into mysql, and restructured the tables to my liking. Some things that I'm having trouble with are:

 

1.)  Keeping everything clean and easy. The database as it sits now has a Customer table, which holds all customer information (customer numbers, contact info, unique ids, etc), a Machine Table which holds serial numbers, copy counts, and links the machine to the customer (it has a field for customer number that ties in to the customer table), and a third table for Service Calls (tied to machines, uses the serial number).

 

Is there an easier way to structure this, or does this seem pretty simple as-is? We move machines around, add new machines or get rid of old ones, create and delete customers, and we have to clear service calls (finish fields in the database and set from open to done.)

 

2.)  I'm writing everything from scratch essentially. I wonder if there is something out there already made that I can pick from or base my code on at the very least. Is there a better language or database that I should be looking into? Or is there a way I can simply speed up the access queries (taking 10-30 seconds now, whereas the php/mysql version is near instant..)

 

I have googled to try and find something that would work, but my google zen didn't pay off this time.

 

 

I think I just need some guidance from someone who has taken on something like this before.

 

Sorry for the wall of text. Any help or guidance would be greatly appreciated.. My users are growing weary and want something done sooner than later.

 

Thanks,

-Jamie

[DavidAM]

Any database is going bog down if you don't maintain it.  Every now and then the data and index pages need to be rebuilt just to keep things running optimum.  Access has a command to Compact the database.  You should run this command to optimize space.  Even if you move it to mySql, or any other database, you will need to run maintenance commands periodically or it will begin to degrade as well.

 

Make sure you have indexes that make since for your queries.  If you often query by Serial Number, you might want to consider adding an index on it.  Indexes do not have to be unique, and they can be composite (i.e made up of multiple fields).  Indexes take up additional disk space, so you have to balance speed for space (but space is cheap these days). 

 

Define foreign keys on your tables: The Customer Number in the Machine table is a foreign key (to the Customer table).  Depending on your version of Access (or any other database), this automatically indexes the column; which makes sense.  Think about this carefully, you can define a foreign key to prevent deletion of the parent (Customer) record if there are child (Machine) records.  However, you can also define the foreign key to Cascade Deletes - if you delete the Customer it automatically deletes the Machines.

 

Having said that, do you really want to delete customers or machines.  You lose history that way.  Consider adding a flag "InActive" to the tables rather than deleting.

[aesthetics1]

David,

 

Thanks a lot for your input.. Going to try and run the compact and repair tool this afternoon when everyone's off of the system. Hopefully that will take care of the sluggishness of the system, however I'd still like to rebuild it soon...

 

Anything you recommend reading about database maintenance? Whether it be for MySQL or Access? Sounds like something I should be reading up on.

 

Thanks!

[aesthetics1]

I ran the Compact and Repair tool in Access 2007. It completed near instantly. Not sure if this is normal, and I suppose I won't know if it had any effect until my users let me know...