Creating help desk system

[davesmith202]

I have a website where potential Customers can submit an enquiry that gives them a Case No. I receive this as an email which is then parsed into an Access table. When somebody Orders from me, I also receive an email which I pass into a different Access table.

Now I am trying to think how I should structure the database. Should it be around the Case No? If I try to do it around the Customer Name, they could enter an enquiry slightly differently each time and therefore not be able to match up the Customer Names. e.g. they enter Speedster inc. the first time and just Speedster when they order. So, my current thoughts are to do everything around the Case No.

But when they order, should I try to push that Order data into the same table that stores the enquiries? Or should it go into a seperate table and have them linked in some way? The Orders table stores Case No, Client name and address, amount and so on.

I would also like to track the Enquiries in some way, perhaps using a combo box that has the following options: Form Completed; File Uploaded; Report Sent;(maybe also Sale Made).

Since I have little experience in help desk systems I wondered if somebody here could shed some light on this before I start programming in earnest, only to find I have an inferior structure.

Potential problems include:

1. Customer puts in the wrong Case No when ordering, so the Case No's don't match and therefore cannot automatically match the enquiries. Do I end up with an orphaned record?

2. Sometimes the Enquiry process produces one Case No. Then the customer resubmits the form again and it produces another Case No because they think the first did not submit. I end up with several Case No's and wondered if this could produce a problem.

Look forward to your feedback.

Regards,

Dave

 

[Mile-O]

As far as I see it you don't build around anything.

You still make your tables as the entities of the system you are modelling requires.

A table for Orders, a table for Cases, a table for Customers, etc..

 

[davesmith202]

Each email I receive, whether from the initial Enquiry state or Order stage has the customer details and Case No. all lumped in together.

Are you saying I should strip out the Company data into one table, the Case No into another and Orders into another? Perhaps a Company table which can have many Case Nos and also many Orders?

I am a little unsure what the structure should be for a simple help desk system.

Dave