I have come up with a project to design a web-based Complaints and Suggestion System for a local estate agents in my area and I am going to use PHP with a MySQL database and at the moment I am doing all my Process and Data Analysis before moving onto to the conceptual and logical design of my database.
I've been asking various professionals for advice and guidance as I am currently on a first going into this year and want to produce an excellent project.
Be interested to have your input or any ideas you may have about it. I'l attach my Project Requirements that I have come up with after my research but any advice on table structure at this point would be most helpful.
My User Requirements are as follows.....
(1)The Customer Interface
* Customers should have an easy way of accessing their details by entering their surname and post code. There should be a facility for them to enter their details if they do not appear, as new customers may not yet be on the system.
* Customers should have a free-text area to type in details of their notification. They should be encouraged to select from a list of types and sub-types the main description which applies to the notification.
* If there is no recorded e mail address for the customer on file they will be asked if they want to input one.
* Once the notification has been submitted the customer should be thanked for taking the trouble to communicate and told that an acknowledgement will be sent within one working day by e mail or post (if they don’t have an e mail address)
(2)The Customer Service Interface
* Submitted complaints are automatically entered into the database. Every day, the customer services manager (CSM) will analyze all notifications received. A list of the complaints should show on her system at login.
* The CSM will select the notifications one at a time and may adjust the types allocated by the customer. She will decide if the notification is a complaint, compliment or suggestion and allocate the appropriate category. She will then choose an acknowledgement letter from a pre-defined list of Word documents – the letter will be generated in Word with the customer’s name and address automatically entered. The acknowledgement will either be printed for posting or sent by e mail if the customer has an e mail address.
* If the notification needs no further action, the CSM will mark it as resolved. If it needs further action she will allocate it to one of the team of Resolvers (i.e. people working at the different agencies), she will give it a turn-round date (a default has been agreed at 5 working days but this can be over-ridden) All notifications allocated to a Resolver will show on a list when s/he logs in. For each notification s/he should either:
Deal with it and contact the customer saying what has been done – stored standard letters may be used for communications with members
Pass it on to another Resolver with a note of what action is required
If the Resolver considers the notification has been dealt with s/he should mark it as resolved.
* Every action taken relating to a notification should be recorded in an Actions list in the database.
(3)The Reporting Interface
* Management would like access to a number of standard reports:
*All notifications by category and type
*Outstanding notifications by category and type
*Outstanding notifications by Resolver
*A summary table showing numbers of complaints by type and sub type over a given period
It's a good thing that you are doing exctensive needs analysis before starting. The thing is that most of what you've posted here related to function and user interface design, not database design. Yes, it becomes clear from what you write what SOME of the fields are, but it's far from clear what kinds of information you need to have, both in the long and short term.
Some questions I'd ask (you already have some parts of this):
What kind of information do they need?
What are they going to do with it?
Immediate reponse - what will be recorded?
What kinds of reports will be run on an ongoing basis?
What kinds of analysis are they going to do? (eg average actual response vs assigned response time, average response time for various types of problems)
What about data integrity?
Data that makes sense
Once you have that kind of information you have a better chance of dealing with your database design properly.