Database Advice
 

Hi,

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 at university 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.....

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)

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.

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


Emerging Database Design

========================
Person_Table
========================
Person_ID
First_Name
Last_Name
Username
Password
PersonType_ID
========================



========================
PersonType_Table
========================
PersonType_ID
Person_Type
========================



========================
Contact_Table
========================
Contact_ID
Contact_Location
Street
City
Post_Code
Region
Country
Telephone_No
Email_Address
========================



========================
Person_Contact_Table
========================
Person_Contact_ID
Person_ID
Contact_ID
========================



========================
Notification_Table
========================
Notification_ID
Person_ID
NotificationType_ID
NotificationSubType_ID
Details
Notification_Date
Target_Date
Status_Type
========================



=======================
NotificationType_Table
=======================
NotificationType_ID
Notification_Type
=======================



==========================
NotificationSubType_Table
==========================
NotificationSubType_ID
Notification_SubType
==========================



========================
StatusType_Table
========================
StatusType_ID
Status_Type
========================



========================
Person_Notification_Table
========================
Person_Notifications_ID
Notification_ID
Person_ID
========================



===================
Action_Table
===================
Action_ID
Notification_ID
ActionType_ID
Action_Details
ActionDate
===================



===================
ActionType_Table
===================
ActionType_ID
ActionType
===================



===================
Note_Table
===================
Note_ID
Note_Details
NoteDate
===================